SAS proc sql - Convert ddmmmyyyy to week-yr and month-yr

Question

I have imported some data into SAS from some Excel spreadsheets sent to me. When I view the output from the imported table, the date appears as "01APR2014" and maintains chronological order. When I view the column properties the type is "Date" and the length is 8. Both the format and informat are DATE9.

I need to be able to convert this date to week-year and month-year, but no matter what I try I always get Jan, 1960.

Using proc sql, I used the below to get the week-year,

"(put(datepart(a.fnlz_date),weeku3.))|| "-" ||(put(datepart(a.fnlz_date),year.)) as FNLZD_WK_YR,"

but all I got was "W00-1960". I've used the formula above successfully many times before with SAS datetime values.

For month-yr, using proc sql, I tried

"datepart(a.fnlz_date) as DT_FNLZD format=monyy.,"

but the only value returned is "JAN60".

I also tried using SUBSTR, but got an error saying it requires a character argument, so SAS must see it as a number at least.

My question; does anyone know a way to get the week-yr and/or month-yr from this format? If so, how? I'm not opposed to using a data step, but I haven't been able to get that to work either.

Thanks in advance for any help or insight provided.

Joe Joe · Accepted Answer · 2014-08-19T19:42:44

datepart converts datetimes to dates. Not helpful here.

If you're just displaying this, then you have a few options, particularly for month. You can just change the format of the variable (This changes what's displayed, but not the underlying value; consider this a value label).

When you use this like this (again, it looks like you got most of the way there):

proc sql;
  select datevar format=monyy5. from table;
quit;

Just don't include that datepart function call as that's not appropriate unless you have a datetime. (Date=# of days since 1/1/1960, Datetime = # of seconds since 1/1/1960:00:00:00).

That will display it with MONYY5. format, which would be MAY10 for May, 2010. You have some other format options, see the documentation on formats by category for more details.

I can't think of a Week format that matches what you want (there are week formats, like WEEKW., as you clearly found, but I don't know that they do exactly what you want. So, if you want to build one yourself, you can either build a custom picture format, or you can make a string.

Building a custom picture format isn't too hard; see the documentation on Picture formats or google SAS Date Picture Format.

proc format;
 picture weekyear (default=8)
  low-high = 'W%0U-%Y' (datatype=date) ;    
quit;

Now you can use that as a normal format.

To get at the week/etc. to build values, you can also use functions week(), month(), etc., if that's easier.

SAS proc sql - Convert ddmmmyyyy to week-yr and month-yr

2 Answers