Difference between Two dataset variable values _2

[AnnaNZ]

Hi

I have asked the forum this question last week, and got a solution that mostly works.

the question was:

The solution that works almost  and was posted on here is:

The appraoch does in principle resolve my issue however, it delets all REFER_IDs that have no timedifference, meaning,it deletes all the enties where   LST_UPDT_DT  =  REFER_DT 

How can I implement a KEEP statement ? Something like keep REFER_ID if   LST_UPDT_DT  =  REFER_DT 

has anybody some ideas?

Many thanks

[Reeza]

Please post data and code as text not images. To write code to work with your data we'd have to type it out and the odds of me doing that are ... none. And then I'd be answering your question now, instead of asking you to provide more information so it's in your best interest to do so 😉

[AnnaNZ]

proc sort data= Kea.decisiontime2; by CL_ID; run;
 proc sql;
  create table Kea.decisiontime3 as 
  select * from 
  (select *, (LST_UPDT_DT  - REFER_DT ) as difference from Kea.decisiontime2 )
  group by REFER_ID
  having difference = max(difference);
  quit;
  proc sort data= Kea.decisiontime3; by CL_ID; run;

REFER_ID	ROLE_IN_REFERRAL	REFER_VAR	LST_UPDT_DT	REFER_DT
1532403	sx	3838	16OCT01:13:47:55	16OCT01:12:43:57
1647594	al	210995	06OCT03:10:36:35	04OCT03:00:00:00
1647594	vi	991712	15OCT03:11:28:32	04OCT03:00:00:00
1728512	al	204246	10JAN05:08:44:06	08JAN05:00:00:00
1728512	sx	208608	10JAN05:09:56:48	08JAN05:00:00:00
1619922	sx	275555	24APR03:16:23:51	21APR03:11:51:16
1648224	vi	699	08OCT03:11:51:57	08OCT03:11:40:18

[AnnaNZ]

Above are the code and the dataset used. However, the code eliminates the first entry, when I wanted there only the refer_DT itself to remain, that is 16OCT01:12:43:57. What can I do?

[ballardw]

---

@AnnaNZ wrote:

Above are the code and the dataset used. However, the code eliminates the first entry, when I wanted there only the refer_DT itself to remain, that is 16OCT01:12:43:57. What can I do?

---

First decide what the actual requirement is. The two bits of code you posted differ in that one request keeping the one with the MIN difference (which I can't show because it was an image) and the other the MAX difference:

having difference = max(difference);

Which raises the question of which one was actually used.

[AnnaNZ]

That is true, I found out that I actually need the max time as I need to find out, which is the most recent time that a certain case was reported again:

So I needed the record with the last update date: LST_UPDT_DT: 15OCT03:11:28:32

REFER_ID	ROLE_IN_REFERRAL	REFER_VAR	LST_UPDT_DT	REFER_DT
1647594	al	210995	06OCT03:10:36:35	04OCT03:00:00:00
1647594	vi	991712	15OCT03:11:28:32	04OCT03:00:00:00

However, at the same time it is probably this MAx condition that deletes the frist record :

1532403

sx

3838

16OCT01:13:47:55

16OCT01:12:43:57

Create two datasets one max and one min and merge them again? Or is there a better solution?

Many thanks.

REFER_ID	ROLE_IN_REFERRAL	REFER_VAR	LST_UPDT_DT	REFER_DT
1532403	sx	3838	16OCT01:13:47:55	16OCT01:12:43:57

[kiranv_ · Accepted solution]

if you want min and max then may be this. I have 

proc sort data= Kea.decisiontime2; by CL_ID; run;
 proc sql;
  create table Kea.decisiontime3 as 
  select * from 
  (select *, (LST_UPDT_DT  - REFER_DT ) as difference from Kea.decisiontime2 )
  group by REFER_ID
  having difference = max(difference)
or difference =min(difference);
  quit;

 if top things does work then 

 proc sql;
  create table Kea.decisiontime3 as 
  select * from 
  (select *, (LST_UPDT_DT  - REFER_DT ) as difference from Kea.decisiontime2 )
  group by REFER_ID
  having difference = max(difference)
union all
select * from kea.decisiontime2
where lst_updt - refer_dt = 0;