topic Re: Need help with code in SAS Programming

Need help with code

Gil_ — Fri, 31 Aug 2018 18:18:39 GMT

I have a code gives me last services
Proc sql;
Create table temp as select
A.id,
A.seq,
A.day,
B.date1 as date2,
Abs(a day-b.date1) as diff
From paper as a
Left join dep1 as b
On a.id=b.id
Group by a.id
Order by id seq, diff

Proc sql;
Create table want
As select id,day,date2, seq
From temp
Group by id , seq
Having min(diff)
;quit;

Data want;
Set temp;
By id seq
If first.seq;
Run;

It works 80%of time

Id. Seq. Day. Date2. Diff
A1. 9401. 05/18/2018. 05/25/2018. 7

What i expect was
Id. Seq. Day. Date2. Diff
A1. 9401. 05/18/2018. 04/27/2018. 21

What im looking for last day there was a service from 5/18/2018. Whichj is 04/27/2018

Re: Need help with code

HB — Fri, 31 Aug 2018 18:40:46 GMT

Some sample data and explanation would really improve things. Paper? Dep1? These things have meaning?

Without investing too much, I'll hazard that right now you are finding the closest date before or after and what you really want is the closest date before. I'll further hazard that a WHERE clause like "where table1.date < table2.date" might move you in that direction.

Re: Need help with code

Gil_ — Fri, 31 Aug 2018 18:54:16 GMT

Sorry abt that my lack of explaining ... paper is order of ticket day and dep is date2 of service

Whst im looking for is based on the day of paper ticket day table when was the last date2 service from the dep1 table
These 1st 3 col come paper. Date2 comes from dep1
Id. Seq. Day. Date2. Diff
A1. 9401. 05/18/2018. 04/27/2018. 21

I think my issue might be seq number

Re: Need help with code

Gil_ — Fri, 31 Aug 2018 21:00:04 GMT

Ok got it to work. I do have a question
Id. Day. Date2. Seq. Diff
Id1. 05/18/2018. 05/15/3018. 9401. 3

Id1. 05/18/2018. 04/27/3018. 9401. 21

Id1. 05/18/2018. 04/11/3018. 9401. 37

Id1. 05/18/2018. 04/02/3018. 9401. 46

How do i extract
Id1. 05/18/2018. 05/15/3018. 9401. 3
And disregard the rest ...

Re: Need help with code

ballardw — Fri, 31 Aug 2018 22:05:50 GMT

My basic approach would look something like this if I understand the rule:

proc sort data=have;
   by id descending day descending date2;
run;

data want;
   set have;
   by id ;
   if first.id;
run;

Re: Need help with code

Gil_ — Fri, 31 Aug 2018 22:41:11 GMT

Thank you