topic Re: Two date formats in the same column, unifying date formats in SAS Programming

Two date formats in the same column, unifying date formats

metallon — Thu, 19 May 2016 11:51:13 GMT

Hi SAS Experts,

I got bad data from the clients, some times the date in column X is formatted as 26.01.2016 sometimes as 2016-02-23.

How can I create a SAS date column that unify's the format into yyyy-mm-dd?

Thanks for any help.

Re: Two date formats in the same column, unifying date formats

Jagadishkatam — Thu, 19 May 2016 12:04:15 GMT

Please try the below code


data want;
set have;
y=input(x,anydtdte10.);
format y yymmdd10.;
run;

Re: Two date formats in the same column, unifying date formats

Kurt_Bremser — Thu, 19 May 2016 12:02:15 GMT

Read the data into a character variable with length 10, and then input into your date variable; select the format according to the occurence of a dash in position 5.

data want;
set have (rename=(L=old_L));
format L yymmddd10.;
if substr(old_L,5,1) = '-'
then L = input(old_L,yymmdd10.);
else L = input(old_L,ddmmyy10.);
drop old_L;
run;

Re: Two date formats in the same column, unifying date formats

metallon — Thu, 19 May 2016 12:07:44 GMT

Kurt, you are amazing!