Converting Character to date in sas when the format is word date

[TheNovice]

Hello all,

Reading from an excel file where the date is in format 25 February, 2019. This is stored as a characer.

Need to convert it to date format in sas.

Any ideas?

Appreciate any direction

[andreas_lds]

You will have to create a new numeric variable to store the date, then use input function with the appropriate informat to convert the string. All in a data-step. The sas online help is not that usable on mobile device, so I can't look up the format-name right now. But I am sure that you could find it yourself.

[andreas_lds · Accepted solution]

It seems that there is no informat matching your date-format, so the string needs to modified first.

data _null_;
   length b $ 9 d 8;
   format d yymmddd10.;

   s = '25 February, 2019';
   b = cats(scan(s, 1, ' '), substr(scan(s, 2, ', '),1, 3), scan(s, 3, ', '));
   d = input(b, date9.);

   put s= / b= / d=;
run;

[TheNovice]

Thank you so much!

It worked!

I got the desired output as:

date = 2019-02-20

Would it be possible for you to briefly explain in step what you did ? I have never used those functions. Really appreciate your help again.

[andreas_lds]

You can find long explanations in the online docs, a useful starting-point is https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.4&docsetId=pgmsashome&docsetTarget=h...

Here are some short and incomplete descriptions:

cats : concatenates strings removing blanks from start and end of each string.

substr(string, start, length) : starting from start, length chars are returned from string

scan(string, n, seps) : returns n-th word from string, seps defines the chars that are word-boundaries.

input : converts string to number by using an informat

[Riteshdell]

try this :

data have;
x='25 February, 2019';
dm=substr(compress(x),1,5);
y=substr(x,length(x)-4,5);
new_date=input(cats(dm,y),date9.);
format new_date date9.;

run;

[TheNovice]

Thank you so much Ritesh... It worked for dates in the tens but not for single digit dates like 2 January, 2019. The date came as blank.

I would not mind the alternative solution. Trying to learn as much as possible

Thanks again for your reply