Date in character format need to change to numeric and then extract month/year/day

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

Date in character format need to change to numeric and then extract month/year/day

Hello - I have this dataset where in a variable is actually a date but sas has read it as character variables with $10 format and informat. I am having issues converting it to numeric variables and then extracting month day year from it.

The variable looks like this - 10/21/2013

i used following code -

data work.test1;

set work.test;

informat LMP $10.;

LMP = input(substr(strip(LMP),1,10),MMDDYY10.);

run;

proc contents data = work.test1;

run;

proc sql;

create table test2 as select input(substr(strip(LMP),1,10),MMDDYY10.)

as LMP format mmddyy10.

from test1;

quit;


Accepted Solutions
Solution
‎05-15-2015 11:16 AM
Super Contributor
Posts: 275

Re: Date in character format need to change to numeric and then extract month/year/day

Posted in reply to new_sas2015

data _null_;

date='10/21/2013';

_date=input(strip(date),mmddyy10.);

year=year(_date);

month=month(_date);

day=day(_date);

put _all_;

run;

View solution in original post


All Replies
Solution
‎05-15-2015 11:16 AM
Super Contributor
Posts: 275

Re: Date in character format need to change to numeric and then extract month/year/day

Posted in reply to new_sas2015

data _null_;

date='10/21/2013';

_date=input(strip(date),mmddyy10.);

year=year(_date);

month=month(_date);

day=day(_date);

put _all_;

run;

Occasional Contributor
Posts: 7

Re: Date in character format need to change to numeric and then extract month/year/day

Thank you slchen! it worked perfectly.  As I am a new SAS user I am curious to know what format does the new variable is created in?

The values for it looks like this - 19653 19657... 19673

also what if the date variable (still read as character variable by SAS wth informat and format $18)  appears like this 29JUL2014:00:00:00.

I tried using this code but no luck.

data frame3;

set work.frame2;

new_DOB = input (strip (DOB), datetime18.);

mm_dob = month(DOB);

dd_dob = day(DOB);

yy4_dob = year(DOB);

put new_DOB;

run;

Super User
Super User
Posts: 7,050

Re: Date in character format need to change to numeric and then extract month/year/day

Posted in reply to new_sas2015

The main problem with your program is the inclusion of the INFORMAT statement.  Why did you add that?  Since this is the first reference to the new LMP variable and you have assigned an CHARACTER informat to the variable SAS will create LMP as a character variable instead of an actual numeric date varaible.

data work.test1;

set work.test;

informat LMP $10.;

LMP = input(substr(strip(LMP),1,10),MMDDYY10.);

run;

What you want instead is to FORMAT the new variable using a date format such as DATE9. or MMDDYY10. or YYMMDD10. so that when you print the data it is recognizable by humans as a date value.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 365 views
  • 0 likes
  • 3 in conversation