topic Re: Partial date char to num in SAS Programming

Partial date char to num

pdhokriya — Tue, 17 Nov 2020 07:46:03 GMT

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.

Re: Partial date char to num

PeterClemmensen — Fri, 13 Nov 2020 13:56:04 GMT

What is your desired result?

Re: Partial date char to num

PeterClemmensen — Fri, 13 Nov 2020 14:01:51 GMT

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;

Re: Partial date char to num

Oligolas — Fri, 13 Nov 2020 14:21:07 GMT

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.

Re: Partial date char to num

data_null__ — Fri, 13 Nov 2020 14:26:53 GMT

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

Re: Partial date char to num

novinosrin — Fri, 13 Nov 2020 15:05:31 GMT

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. 🙂

Re: Partial date char to num

MarkDawson — Fri, 13 Nov 2020 14:33:39 GMT

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.

Re: Partial date char to num

Astounding — Fri, 13 Nov 2020 14:55:57 GMT

Good to know about. I always had to use:

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

Re: Partial date char to num

Kurt_Bremser — Fri, 13 Nov 2020 15:46:46 GMT

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).

Re: Partial date char to num

SASKiwi — Sun, 15 Nov 2020 02:26:00 GMT

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

Re: Partial date char to num

Kurt_Bremser — Sun, 15 Nov 2020 07:04:38 GMT

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.

Re: Partial date char to num

pdhokriya — Fri, 20 Nov 2020 13:06:56 GMT

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.

Re: Partial date char to num

Kurt_Bremser — Fri, 20 Nov 2020 13:30:58 GMT

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.