SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Difference between Two dataset variable values _2

Accepted Solution Solved
Reply
Contributor
Posts: 58
Accepted Solution

Difference between Two dataset variable values _2

Hi

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

the question was:

question 1.JPG

 

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

solution.JPG

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


Accepted Solutions
Solution
‎06-22-2017 04:18 PM
PROC Star
Posts: 326

Re: Difference between Two dataset variable values _2

[ Edited ]

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;


 

 

 

View solution in original post


All Replies
Super User
Posts: 19,868

Re: Difference between Two dataset variable values _2

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 Smiley Wink

Contributor
Posts: 58

Re: Difference between Two dataset variable values _2

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_IDROLE_IN_REFERRALREFER_VARLST_UPDT_DTREFER_DT
1532403sx383816OCT01:13:47:5516OCT01:12:43:57
1647594al21099506OCT03:10:36:3504OCT03:00:00:00
1647594vi99171215OCT03:11:28:3204OCT03:00:00:00
1728512al20424610JAN05:08:44:0608JAN05:00:00:00
1728512sx20860810JAN05:09:56:4808JAN05:00:00:00
1619922sx27555524APR03:16:23:5121APR03:11:51:16
1648224vi69908OCT03:11:51:5708OCT03:11:40:18
Contributor
Posts: 58

Re: Difference between Two dataset variable values _2

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?

Super User
Posts: 11,343

Re: Difference between Two dataset variable values _2


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.

Contributor
Posts: 58

Re: Difference between Two dataset variable values _2

 

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_IDROLE_IN_REFERRALREFER_VARLST_UPDT_DTREFER_DT
      
1647594al21099506OCT03:10:36:3504OCT03:00:00:00
1647594vi99171215OCT03:11:28:3204OCT03:00:00:00

 

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

1532403sx383816OCT01:13:47:5516OCT01:12:43:57

 

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

Many thanks.

 

REFER_IDROLE_IN_REFERRALREFER_VARLST_UPDT_DTREFER_DT
1532403sx383816OCT01:13:47:5516OCT01:12:43:57
Solution
‎06-22-2017 04:18 PM
PROC Star
Posts: 326

Re: Difference between Two dataset variable values _2

[ Edited ]

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;


 

 

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 173 views
  • 1 like
  • 4 in conversation