topic Re: Recoding Date in SAS Programming

Recoding Date

TXSASneophyte — Mon, 03 Oct 2016 17:48:00 GMT

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.

Re: Recoding Date

JediApprentice — Mon, 03 Oct 2016 20:25:46 GMT

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;

Re: Recoding Date

TXSASneophyte — Mon, 03 Oct 2016 19:02:05 GMT

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