topic Re: Comparing dates in proc sql in SAS Procedures

Comparing dates in proc sql

Walternate — Thu, 24 Oct 2013 15:56:14 GMT

Hi all,

I am trying to create a dataset where a date found in Dataset2 is later than a date found in Dataset1.

My code looks like this:

proc sql;

create table new as

select var1, var2, datepart(var3) format date9. as datevar, datevar2

from Dataset1 a

inner join dataset2 b

on a.var1=b.var2

where datevar>datevar2;

quit;

I don't get an error message but the program ignores the date equation and performs the join regardless of whether the date from Dataset2 is earlier or later than the date in Dataset1.

The datepart is to get var3 out of date/time format (mm/dd/yyhh:mm:ss) and into the same format as datevar2.

Any help is much appreciated.

Re: Comparing dates in proc sql

Reeza — Thu, 24 Oct 2013 16:09:46 GMT

Are you sure both dates are in SAS Date formats?

Can you post the log and sample data.

Re: Comparing dates in proc sql

Jagadishkatam — Thu, 24 Oct 2013 16:16:15 GMT

Please try the untested code,

proc sql;

create table new as

select a.var1, b.var2, datepart(a.var3) as datevar format=date9., b.datevar2

from Dataset1 a

inner join dataset2 b

on a.var1=b.var2

where datepart(a.var3)>b.datevar2;

quit;

Thanks,

Jagadish

Re: Comparing dates in proc sql

snoopy369 — Thu, 24 Oct 2013 16:17:58 GMT

Depending on what you actually want, this should work (mostly) if you add the calculated keyword. Without that keyword the query should throw an error in your log. It may or may not give you what you actually want, depending on your data, but it should do what you're asking.

data dataset1;

format var3 datetime.;

do var1=1 to 5;

var3=datetime() + floor(ranuni(7)*11)*86400; *current datetime plus zero to ten days;

output;

end;

run;

data dataset2;

format datevar2 date9.;

do var2 = 1 to 5;

do _n = 1 to 4;

datevar2 = today() + floor(ranuni(7)*5); *current date plus zero to 4 days;

output;

end;

run;

proc sql;

create table new as

select var1, var2, datepart(var3) format date9. as datevar, datevar2

from Dataset1 a

inner join dataset2 b

on a.var1=b.var2

where calculated datevar>datevar2;

quit;

Re: Comparing dates in proc sql

Vince28_Statcan — Thu, 24 Oct 2013 16:27:13 GMT

My first thought is that condition

where datevar>datevar2 is the opposite of the condition you mentioned you are trying to achieve.