StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>This is one possible solution - breaking it up into "1st" "April" "2013", and then constructing a date from "April" "2013", finally using "1st" and the <code>intnx</code> function to advance that number of weeks. You may need to adjust the call to <code>intnx</code> depending on how you define "1st week of April" (in the example that starts on 3/31 for example, if your company defines that as the week from 4/7 to 4/13 that's different, and if you need week to start on a different day of the week that's also an adjustment - look at the <a href="http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000212700.htm" rel="nofollow">documentation</a> for more information). </p> <pre><code>data have; infile datalines truncover; input @1 week_of_the_promotion $25.; datalines; 1st April 2013 1st April 2013 3rd April 2013 3rd April 2013 5th April 2013 ;;;; run; data want; set have; weeknum = compress(scan(week_of_the_promotion,1),,'kd'); *Note 1; for_date = '01'||substr(scan(week_of_the_promotion,2),1,3)|| scan(week_of_the_promotion,3); *Note 2; actual_date = intnx('week',input(for_date,date9.),weeknum-1,'b'); *Note 3; format actual_date date9.; put week_of_the_promotion= actual_date=; run; </code></pre> <ol> <li><code>Scan</code> separates the string into words by a delimiter, by default space, and returns the word you ask for (1, here). <code>Compress</code> removes/keeps specific kinds of characters; here I use it to "keep" "digits". So <code>scan</code> returns "1st" and then I tell it to keep only digits, so "1".</li> <li>Here we use <code>scan</code> twice, to get "April" and "2013". April is cut using substr to the first 3 characters, "Apr", because that is how SAS dates are most easily read. Then we append the year to the end, and "01" to the beginning (for 1st day in ...). This ends up being "01APR2013", which is then <code>input</code> using a date informat to a number (DDMONYYYY is SAS's default date in/format).</li> <li>We use <code>intnx</code> here, which advances a date a certain number of date-intervals. We are using "week" to advance it a certain number of weeks, and advancing the number stored in "weeknum" (from note 1) minus 1 (since week 1 = advance 0 weeks, week 2 = advance 1 week, etc.) The "b" is "Beginning" of week (as opposed to 'end' or 'middle' or 'same'). "Week" is a default week starting on sunday; WEEK.2 would shift that forward two days to be weeks starting on Tuesdays, etc.</li> </ol>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload