Hello folks !
I would like to seek your help to solve my question ! I found many threads and documentation on this subject, but none solved my problem.
My existing variable format is Char "12021988" and I want to convert it to SAS DDMMYY10. or date9. format.
I am strating from the variable "Date_Naissance" and extracting each day, month and year, concatinating all the three to get a new variable "Date_Naissance_1".
I used the below code :
proc sql;
create table Test_Naiss as select DATE_NAISSANCE,substr(DATE_NAISSANCE,5,3) as MONTH,
(case when calculated MONTH="Jan" then "01"
when calculated MONTH="Feb" then "02"
when calculated MONTH="Mar" then "03"
when calculated MONTH="Apr" then "04"
when calculated MONTH="May" then "05"
when calculated MONTH="Jun" then "06"
when calculated MONTH="Jul" then "07"
when calculated MONTH="Aug" then "08"
when calculated MONTH="Sep" then "09"
when calculated MONTH="Oct" then "10"
when calculated MONTH="Nov" then "11"
when calculated MONTH="Dec" then "12"
else ""
end) as MONTH_1,
substr(DATE_NAISSANCE,9,2) as DAY,
substr(DATE_NAISSANCE,25,4) as YEAR,
cats(calculated DAY,calculated MONTH_1,calculated YEAR) as Date_Naissance_1,
input(calculated Date_Naissance_1,10.) as Date_Naissance_2,
input(calculated Date_Naissance_1,DDMMYY10.) as Date_Naissance_3
From inputs.production082020;
Quit;
But I can not get the format needed.
Any help will be appreciated. Thanks in advance.
Actually I think your date_naissance_3 is correct but you did not assign a format to the value. So assign Format=ddmmyy10.
I don't think that you are stating your objective clearly. If the objective is to turn that really ugly DATE_NAISSANCE into an actual date then get the numeric values of month, day and year then use the SAS function MDY to create a date value.
Something like this might work (obviously untested as I don't have your data)
proc sql; create table Test_Naiss as select DATE_NAISSANCE,substr(DATE_NAISSANCE,5,3) as MONTH, (case when calculated MONTH="Jan" then 01 when calculated MONTH="Feb" then 02 when calculated MONTH="Mar" then 03 when calculated MONTH="Apr" then 04 when calculated MONTH="May" then 05 when calculated MONTH="Jun" then 06 when calculated MONTH="Jul" then 07 when calculated MONTH="Aug" then 08 when calculated MONTH="Sep" then 09 when calculated MONTH="Oct" then 10 when calculated MONTH="Nov" then 11 when calculated MONTH="Dec" then 12 else . end) as MONTH_1, input(substr(DATE_NAISSANCE,9,2),best.) as DAY, input(substr(DATE_NAISSANCE,25,4),best.) as YEAR, MDY(month_1,Day,Year) as Date_Naissance_3 Format=ddmmyy10. From inputs.production082020; Quit;
Actually I think your date_naissance_3 is correct but you did not assign a format to the value. So assign Format=ddmmyy10.
I don't think that you are stating your objective clearly. If the objective is to turn that really ugly DATE_NAISSANCE into an actual date then get the numeric values of month, day and year then use the SAS function MDY to create a date value.
Something like this might work (obviously untested as I don't have your data)
proc sql; create table Test_Naiss as select DATE_NAISSANCE,substr(DATE_NAISSANCE,5,3) as MONTH, (case when calculated MONTH="Jan" then 01 when calculated MONTH="Feb" then 02 when calculated MONTH="Mar" then 03 when calculated MONTH="Apr" then 04 when calculated MONTH="May" then 05 when calculated MONTH="Jun" then 06 when calculated MONTH="Jul" then 07 when calculated MONTH="Aug" then 08 when calculated MONTH="Sep" then 09 when calculated MONTH="Oct" then 10 when calculated MONTH="Nov" then 11 when calculated MONTH="Dec" then 12 else . end) as MONTH_1, input(substr(DATE_NAISSANCE,9,2),best.) as DAY, input(substr(DATE_NAISSANCE,25,4),best.) as YEAR, MDY(month_1,Day,Year) as Date_Naissance_3 Format=ddmmyy10. From inputs.production082020; Quit;
Your data is well formatted, so you're in luck.
I don't have all you data, but I typed in the first three. Below is my program and below that my results. I think this might be a simpler approach than all the CALCULATED fields you're using. See what you think.
data Dates_From_Text;
LENGTH Text_Date $32;
INPUT Text_Date & $;
DATALINES;
Fri Feb 12 00:00:00 WAT 1988
Sat Aug 15 00:00:00 WAT 1970
Tue May 14 00:00:00 WAT 1985
;
RUN;
PROC SQL;
SELECT INPUT(CATS( SUBSTR(Text_Date, 9, 2),
SUBSTR(Text_Date, 5, 4),
SUBSTR(Text_Date, LENGTH(Text_Date) - 4),
'd')
, DATE9.) AS SAS_Date FORMAT mmddyyd10.
FROM Dates_From_Text;
QUIT;
Results. I formatted the results as mmddyyd10, but you can format them any way you like. These are standard numeric SAS dates.
Jim
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.