Help using Base SAS procedures

Comparing dates in proc sql

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 138
Accepted Solution

Comparing dates in proc sql

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.


Accepted Solutions
Solution
‎10-24-2013 12:16 PM
Trusted Advisor
Posts: 1,131

Re: Comparing dates in proc sql

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

Thanks,
Jag

View solution in original post


All Replies
Super User
Posts: 17,912

Re: Comparing dates in proc sql

Are you sure both dates are in SAS Date formats?

Can you post the log and sample data.

Solution
‎10-24-2013 12:16 PM
Trusted Advisor
Posts: 1,131

Re: Comparing dates in proc sql

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

Thanks,
Jag
Regular Contributor
Posts: 244

Re: Comparing dates in proc sql

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;

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;

Super Contributor
Posts: 339

Re: Comparing dates in proc sql

My first thought is that condition

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

☑ This topic is solved.

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

Discussion stats
  • 4 replies
  • 5805 views
  • 6 likes
  • 5 in conversation