DATA Step, Macro, Functions and more

converting character date to numeric form if only year and month are provided

Reply
Super Contributor
Posts: 272

converting character date to numeric form if only year and month are provided

Dear,

 

In my data, for some of the date values  only year and month and for some only year are provided. what informat I need to use to convert to numeric form.

 

DATE

2016-06-12

2016-06

2016

 

I use the code: I am not getting the output for observation 2 and 3.  For 2 and 3 observations, i need 2016-06-01 and 2016-01-01 as my output. Please help. Thank you

 

date1=input(date, yymmdd10.);

format AESTDT yymmdd10.;

Super User
Posts: 5,518

Re: converting character date to numeric form if only year and month are provided

Posted in reply to knveraraju91

aestdt = input( cats(date, '-01-01'), yymmdd10.);

format aestdt yymmdd10.;

Super Contributor
Posts: 272

Re: converting character date to numeric form if only year and month are provided

Posted in reply to Astounding

Thanks for the help. If I have to convert 2016-06(CHARARCTER) to numeric form 2016-06 what is the informat I have to use.

 

date1=informat(DATE,?); 

 

I have been looking in SAS material. I could not find it. How to convert. Thanks.

Super User
Posts: 5,518

Re: converting character date to numeric form if only year and month are provided

[ Edited ]
Posted in reply to knveraraju91

You would use exactly what I posted.  Informats always read a character string (whether or not the character string contains digits).

 

There is no such thing in SAS as a numeric form 2016-06.

Super Contributor
Posts: 272

Re: converting character date to numeric form if only year and month are provided

Posted in reply to Astounding

Once again Thank you very much. But that did not help me for my output. The following what i been trying.

 

I been trying to derive date1 from date and date3 variables

 

If date is complete then date1=date (observation1);

if only year and month provided in date variable then compare date variable month to date2 variable month. If monthes are equal then date1=date2(observation3); else date1=first day of month(observation2).

 

if only year provided, then compare with date variable year to date2 variable year. If years are equal then date1=date2(observation5); else date1=first day of year(observation4). please help. Thank you.

 

 

 

date(Character)    date1                                    date2(numeric)
2015-06-03         2015-06-03                        2015-02-03

2015-06             2015-06-01                          2015-05-04

2015-05             2015-05-04                         2015-05-04

2015                 2015-01-01                           2014-04-04

2015                  2015-02-01                         2015-02-01

Super User
Posts: 5,518

Re: converting character date to numeric form if only year and month are provided

Posted in reply to knveraraju91

OK, here's a possibility ... within the context of a DATA step ...

 

if length(date)=10 then date1 = input(date, yymmdd10.);

else do;

   if trim(date) =: put(date2, yymmddd10.) then date1 = date2;

   else date1 = input( cats(date, '-01-01'), yymmdd10.);

end;

format date1 yymmddd10.;

 

It's untested, so it might need a little debugging.  Note a couple of items about the ELSE DO statement.  It assumes that you will always have something in DATE.  If it might be blank, that's another condition that needs to be accounted for.  Also, it's tricky but handles both cases (year + month, or just the year).

Super Contributor
Posts: 272

Re: converting character date to numeric form if only year and month are provided

Posted in reply to Astounding

Great. Thank you

Ask a Question
Discussion stats
  • 6 replies
  • 298 views
  • 3 likes
  • 2 in conversation