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.
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;
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;
That did the trick! Thank you so much for the help. 😄
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.