BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Walternate
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Jagadishkatam
Amethyst | Level 16

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

4 REPLIES 4
Reeza
Super User

Are you sure both dates are in SAS Date formats?

Can you post the log and sample data.

Jagadishkatam
Amethyst | Level 16

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
snoopy369
Barite | Level 11

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;

Vince28_Statcan
Quartz | Level 8

My first thought is that condition

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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