Help using Base SAS procedures

Convert date to week-yr, month-yr

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

Convert date to week-yr, month-yr

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.


Accepted Solutions
Solution
‎08-19-2014 03:14 PM
Respected Advisor
Posts: 4,663

Re: Convert date to week-yr, month-yr

Your data is already into a date format. The DATEPART function is for converting DATETIME values (numbers of seconds since 01JAN1960) into DATE (number of days since 01JAN1960). Using DATEPART on DATEs will divide the number of days by 86400 and bring your values very near 01JAN1960.

Just drop the DATEPART function and associate appropriate formats such as weeku5. or MONYY7, with your variables.

PG

PG

View solution in original post


All Replies
Super User
Posts: 17,963

Re: Convert date to week-yr, month-yr

There is no TYPE=Date, only text and number. Which is yours?

Why are you using datepart, if it is a date, datepart is for datetime variables? Try removing the datepart and see if that works.

Solution
‎08-19-2014 03:14 PM
Respected Advisor
Posts: 4,663

Re: Convert date to week-yr, month-yr

Your data is already into a date format. The DATEPART function is for converting DATETIME values (numbers of seconds since 01JAN1960) into DATE (number of days since 01JAN1960). Using DATEPART on DATEs will divide the number of days by 86400 and bring your values very near 01JAN1960.

Just drop the DATEPART function and associate appropriate formats such as weeku5. or MONYY7, with your variables.

PG

PG
Super User
Posts: 17,963

Re: Convert date to week-yr, month-yr

Also, my dogs name is Sigma :smileygrin:

New Contributor
Posts: 2

Re: Convert date to week-yr, month-yr

Thanks for the help! Got it working, now.

Cheers!

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 3328 views
  • 5 likes
  • 3 in conversation