topic Re: help in proc sql code in SAS Programming

help in proc sql code

knveraraju91 — Sun, 02 Aug 2020 17:30:51 GMT

Dear,

Please suggest in my sql code. I am not getting the out put i need and have warning messages in log

I need to check every record in one dataset with two set by id and check if date2 -date1 greater than 4 days

output needed

id date1 date2

1 2018-11-19 2019-12-01
1 2019-10-20 2019-11-10
1 2019-10-20 2019-10-25
1 2019-10-20 2019-12-01
1 2019-10-23 2019-12-01
1 2019-10-23 2019-11-10

data one;
input id date1 $10.;
datalines;
1 2019-10-20
1 2019-10-23
1 2018-11-19
;
data two;
input id date2 $10.;
datalines;
1 2019-10-25
1 2019-11-10
1 2019-12-01
;


proc sql;
create table three as
select * from one as a left join two as b
on a.id=b.id
group by a.id,date1
having input(date2,yymmdd10.)- input(date1,yymmdd10.) gt 4;
quit;

Re: help in proc sql code

Shmuel — Sun, 02 Aug 2020 18:09:57 GMT

You have not show the expected results, so I hope next code will satisfy you:

data one;
input id date1 anydtdte10.;
format date1 yymmdd10.;
datalines;
1 2019-10-20
1 2019-10-23
1 2018-11-19
;
run;

data two;
input id date2 anydtdte10.;
format date2 yymmdd10.;
datalines;
1 2019-10-25
1 2019-11-10
1 2019-12-01
;
run;

proc sql;
create table three as
select coalesce(a.id,b.id) as ID, 
       a.date1, b.date2 
   from one as a left join two as b
on a.id = b.id and
   b.date2 - a.date1 gt 4   
order by a.id,date1
;
quit;

output result:

Obs	ID date1	date2
1	1	2018-11-19	2019-12-01
2	1	2018-11-19	2019-11-10
3	1	2018-11-19	2019-10-25
4	1	2019-10-20	2019-11-10
5	1	2019-10-20	2019-10-25
6	1	2019-10-20	2019-12-01
7	1	2019-10-23	2019-12-01
8	1	2019-10-23	2019-11-10

Re: help in proc sql code

Kurt_Bremser — Mon, 03 Aug 2020 08:43:52 GMT

For a.date = 2018-11-19, ALL dates in TWO satisfy your condition. Why do you keep only the last one, while for other dates you take three or two?