Recoding Date

[TXSASneophyte]

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. 

[JediApprentice · Accepted solution]

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;

[TXSASneophyte]

That did the trick! Thank you so much for the help. 😄