Partial date char to num

[pdhokriya]

Hi Reader,

How can I convert char to num below mentioned data, in final output i do not required partial date.

data new;

date = "2017-07-07" ; output ;
date = "2017"; output ;
date = "2007-11"; output;

run;

Regards

Priya

Thank you.

-----------------------------------------------------

==============================

-----------------------------------------------------

My final output is only 2017-07-07.

2nd and 3rd observation not required as it partial. Requirement is only for full date , which is in char and need to convert in num that is all.

My approch is this -

proc sql ;
create table have as
select case when length (date) = 10 then input(date,anydtdte20.) else . end as date format yymmdd10.
from new ;
quit;

Thank you all for your replies.

[PeterClemmensen]

What is your desired result?

[PeterClemmensen]

Here, I simply assume that if month or day is missing then it is the first day/month..

data want(drop=dt);
   set new;
   dt = catx('-', substr(date, 1, 4), 
             ifc(substr(date, 6, 7), substr(date, 6, 7), '01'), 
             ifc(substr(date, 9, 10), substr(date, 9, 10), '01'));
   date2 = input(dt, yymmdd10.);
   format date2 yymmdd10.;
run;

[Oligolas]

Hi,

conversion of partial date to numeric is not possible in SAS.

You need to discuss the imputation strategy with a statistician and/or build a complete date first as @PeterClemmensen did.

[data_null__]

The B8601DA in-format will impute DTC.

18   data _null_;
19      do dtc="2017-07-07","2017","2007-11";
20         date = input(dtc,b8601da.);
21         put (_all_)(=);
22         end;
23      format date date11.;
24      run;

dtc=2017-07-07 date=07-JUL-2017
dtc=2017 date=01-JAN-2017
dtc=2007-11 date=01-NOV-2007

[novinosrin]

Guru @data_null__  One comment is all I have. It's unfair for you to know everything leave alone me being jealous. That level of knowledge, speed and precision. Jeez!!  Sure you are aware and is nothing new that I would pay attention and take notes to your posts, albeit sometimes I worry that I can't keep up. In a way I am glad, you are not as frequent as you were in the past. 🙂

[Astounding]

Good to know about.  I always had to use:

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

[MarkDawson · Accepted solution]

The ANYDTDTE will handle two of the three strings with no NOTEs/WARNINGs/ERRORs, and a missing value for 2017...

data results ; 
   set new ; 
   date_num = input(date,ANYDTDTE10.) ; 
   put date= date_num= ;
   format date_num date. ;
run ;

It really does depend on what you want for the final results, e.g. for "2017", did you want

• a number 2017
• a SAS Date value 20820 (01JAN2017)
• missing value

If you want just a number, try this...

data results ; 
   set new ; 
   date_num = input(compress(date,,'kd'),10.) ; 
   put date= date_num= ;
run ;

If you want a SAS Date Value, then you'll need to build some extra logic in to add at least a default month, and might be dependent on what other abbreviated values you have.

[pdhokriya]

My final output is only 2017-07-07.

2nd and 3rd observation not required as it partial. Requirement is only for full date , which is in char and need to convert in num that is all.

My approch is this -

proc sql ;
create table have as
select case when length (date) = 10 then input(date,anydtdte20.) else . end as date format yymmdd10.
from new ;
quit;

Thank you all for your reply.

[Kurt_Bremser]

I suggest you use the YYMMDD10. informat after checking for a length of 10. I am wary of the ANY.... informats reading something in without a message when it is in fact nonsense disguising as a date.

[Kurt_Bremser]

You need to talk to your "customers" what they see as best for those partial dates, so that the later use won't skewer their statistics (too much).

[SASKiwi]

Yes, those 1 Jan date frequency spikes are always highly suspect 😉.

[Kurt_Bremser]

With such partial dates, one might even consider to store separate year/month/day values, so the end user can decide how to handle missing months and/or days as is best for their current analysis.