Can't format a date correctly while importing a csv into SAS

Question

I'm importing a csv file into SAS which contains a field which uses a word date format similar to worddatxw. but with letters after the day dates (e.g. 1st instead of 1):

Week_of_the_promotion
1st April 2013
1st April 2013
3rd April 2013
3rd April 2013
5th April 2013

I've got this far:

data work.leafletdata;
    infile "C:\rawdata.csv" 
    delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=2;
    informat
        Week_of_the_promotion *weird informat?* Barcode $20. 
                StartDate mmddyy10.   EndDate mmddyy10. ;
    format
        Week_of_the_promotion *preferably date9.* Barcode $20. 
        StartDate date9.      EndDate   date9.  ;
    input 
        Week_of_the_promotion $  Barcode $  
                StartDate $              EndDate $  ;
run;

I cannot work out how to import this into SAS using proc import and while turning it into a usable date when within SAS .

I'm a SAS beginner, so please use layman's terms. Thanks! :)

Joe Joe · Accepted Answer · 2013-08-01T16:12:34

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 intnx function to advance that number of weeks. You may need to adjust the call to intnx 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 documentation for more information).

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;

Scan separates the string into words by a delimiter, by default space, and returns the word you ask for (1, here). Compress removes/keeps specific kinds of characters; here I use it to "keep" "digits". So scan returns "1st" and then I tell it to keep only digits, so "1".
Here we use scan 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 input using a date informat to a number (DDMONYYYY is SAS's default date in/format).
We use intnx 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.

Can't format a date correctly while importing a csv into SAS

1 Answers