Two date formats in the same column, unifying date formats

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 133
Accepted Solution

Two date formats in the same column, unifying date formats

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.


dates.png

Accepted Solutions
Solution
‎05-19-2016 08:07 AM
Super User
Posts: 7,422

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

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Trusted Advisor
Posts: 1,137

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

[ Edited ]

Please try the below code


data want;
set have;
y=input(x,anydtdte10.);
format y yymmdd10.;
run;
Thanks,
Jag
Solution
‎05-19-2016 08:07 AM
Super User
Posts: 7,422

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

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 133

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

Kurt, you are amazing!
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 321 views
  • 4 likes
  • 3 in conversation