topic Re: dealing with two different date formats in a single column in proc sql in New SAS User

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

Abhinav26 — Tue, 18 Jun 2019 18:50:52 GMT

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 ?

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

Reeza — Tue, 18 Jun 2019 18:54:52 GMT

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?

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

Abhinav26 — Tue, 18 Jun 2019 19:00:07 GMT

value should be June 4, 2019.

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

Abhinav26 — Tue, 18 Jun 2019 19:01:02 GMT

and yes only these two formats i have

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

Kurt_Bremser — Tue, 18 Jun 2019 19:04:48 GMT

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

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

Abhinav26 — Tue, 18 Jun 2019 19:07:20 GMT

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.

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

Abhinav26 — Tue, 18 Jun 2019 19:08:37 GMT

Is there any solution to this ?

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

Reeza — Tue, 18 Jun 2019 19:09:26 GMT

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 ?

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

Abhinav26 — Tue, 18 Jun 2019 19:14:18 GMT

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

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

Reeza — Tue, 18 Jun 2019 19:28:57 GMT

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

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

Abhinav26 — Tue, 18 Jun 2019 21:00:32 GMT

It worked, Thanks