DATA Step, Macro, Functions and more

Recoding Date

Accepted Solution Solved
Reply
Contributor
Posts: 45
Accepted Solution

Recoding Date

Hello Everyone,

 

I am struggling to recode DOB date values into something more useful. I am using PROC SQL to extract data from a larger dataset, and the large dataset has the DOB as "yyyymmdd" format (looks like this:19860222 ) and I want to convert it to date9. format (to look like this: 22FEB1986). My failed code is:

 

PROC SQL;
CREATE TABLE NEW_DATASET AS
SELECT ID,
SEX,
RACE,
DOB INFORMAT = DATE9. FORMAT= DATE9.,

ENC_MNTH
FROM SOURCE_DATASET
WHERE 200701 <= ENC_MNTH<= 201509
ORDER BY ID, ENC_MNTH;
QUIT;

 

Nothing happens to DOB with this code. Does someone have a fixed to the problem? If possible, please keep it in the proc sql format. 

 

I am using SAS 9.4. 

 

Muchas Gracias. 


Accepted Solutions
Solution
‎10-03-2016 03:01 PM
Frequent Contributor
Posts: 123

Re: Recoding Date

[ Edited ]
Posted in reply to TXSASneophyte

Hey, 

 

Since the DOB is a numeric value to begin with, you must first convert it to character that is in the format MMDDYYYY by isolating the the digits for year, month, and day. You can do this by using the substr function along with the input/put functions and then combine all three susbtrings using the cats function. So now we have a modified date input. Now, turn it into a numeric SAS date field by using the input function and anydtdte informat. And finally add a format statement at the end of the select as you did.

 

PROC SQL;
CREATE TABLE NEW_DATASET AS
SELECT ID,
SEX,
RACE,
input(cats(substr(put(DOB,8.),5,2),substr(put(DOB,8.),7,2),substr(put(DOB,8.
),1,4)),anydtdte.) format DATE9. AS DOB
ENC_MNTH
FROM SOURCE_DATASET
WHERE 200701 <= ENC_MNTH<= 201509
ORDER BY ID, ENC_MNTH;
QUIT;

 

View solution in original post


All Replies
Solution
‎10-03-2016 03:01 PM
Frequent Contributor
Posts: 123

Re: Recoding Date

[ Edited ]
Posted in reply to TXSASneophyte

Hey, 

 

Since the DOB is a numeric value to begin with, you must first convert it to character that is in the format MMDDYYYY by isolating the the digits for year, month, and day. You can do this by using the substr function along with the input/put functions and then combine all three susbtrings using the cats function. So now we have a modified date input. Now, turn it into a numeric SAS date field by using the input function and anydtdte informat. And finally add a format statement at the end of the select as you did.

 

PROC SQL;
CREATE TABLE NEW_DATASET AS
SELECT ID,
SEX,
RACE,
input(cats(substr(put(DOB,8.),5,2),substr(put(DOB,8.),7,2),substr(put(DOB,8.
),1,4)),anydtdte.) format DATE9. AS DOB
ENC_MNTH
FROM SOURCE_DATASET
WHERE 200701 <= ENC_MNTH<= 201509
ORDER BY ID, ENC_MNTH;
QUIT;

 

Contributor
Posts: 45

Re: Recoding Date

Posted in reply to JediApprentice

That did the trick! Thank you so much for the help. Smiley Very Happy

☑ This topic is solved.

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

Discussion stats
  • 2 replies
  • 289 views
  • 1 like
  • 2 in conversation