topic Re: Difference between Two dataset variable values _2 in SAS Data Management

Difference between Two dataset variable values _2

AnnaNZ — Mon, 19 Jun 2017 22:26:44 GMT

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

Re: Difference between Two dataset variable values _2

Reeza — Mon, 19 Jun 2017 22:36:38 GMT

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 😉

Re: Difference between Two dataset variable values _2

AnnaNZ — Mon, 19 Jun 2017 23:05:17 GMT

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

Re: Difference between Two dataset variable values _2

AnnaNZ — Mon, 19 Jun 2017 23:07:28 GMT

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?

Re: Difference between Two dataset variable values _2

ballardw — Mon, 19 Jun 2017 23:33:15 GMT

@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.

Re: Difference between Two dataset variable values _2

AnnaNZ — Mon, 19 Jun 2017 23:42:23 GMT

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

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

Re: Difference between Two dataset variable values _2

kiranv_ — Tue, 20 Jun 2017 00:29:58 GMT

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;