dealing with two different date formats in a single column in proc sql

[Abhinav26]

Hi,

I am getting two different date formats from the database for single variable.

I want the output to be consistent with mmddyy10. format.

How to do it ?

[Reeza]

The variable is character. Is that the only two formats that you have? For that first one is the date value supposed to be April 6, 2019 or June 4, 2019?

[Abhinav26]

value should be June 4, 2019.

[Abhinav26]

and yes only these two formats i have

[Kurt_Bremser]

---

@Abhinav26 wrote:
and yes only these two formats i have

---

You already received defective data. How can you be sure you won't have further defects when the source has already proven itself to be unreliable?

Return to sender, and require consistent formatting of data.

[Abhinav26]

differently formatted data is based on another variable on the right side, i.e. quest_id. So, there are only these two types of date formats.

[Abhinav26]

Is there any solution to this ?

[Reeza · Accepted solution]

data want;
set have;

x=length(scan(due_date10X. 1, '-'));
if x = 4 then due_date_want = input(due_date10X, yymmdd10.);
else due_date_want = input(due_date10X, mmddyy10.);

run;

First take the first part of the string using SCAn(). If it's a length of 4, that's the YYMMDD format. Otherwise use MMDDYY for the format.

---

@Abhinav26 wrote:

Hi,

I am getting two different date formats from the database for single variable.

I want the output to be consistent with mmddyy10. format.

How to do it ?

---

[Abhinav26]

Thanks but i want new variable with values in mmddyy10. format for both rows and that too using proc SQL.

[Reeza]

---

@Abhinav26 wrote:

Thanks but i want new variable with values in mmddyy10. format for both rows and that too using proc SQL.

---

That's what it does, did you try it? There's no way I can know you're using SQL... (EDIT: My bad, It was in your subject but not in the question. Should be an easy change though)

You can switch the IF/THEN statements to a CASE statement or use the Query builder to replicate the functions using the advanced computation features.

Give it a shot and if you can't figure it out, post your code. 

[Abhinav26]

It worked, Thanks