Converting Char variable to date format

[KarimaTouati]

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.

[ballardw · Accepted solution]

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;

[jimbarbour]

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

[KarimaTouati]

Thank you @ jimbarbour 🙂 that resolved my problem as well.