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.
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
Are you sure both dates are in SAS Date formats?
Can you post the log and sample data.
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
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;
My first thought is that condition
where datevar>datevar2 is the opposite of the condition you mentioned you are trying to achieve.
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.
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.