BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
AnnaNZ
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
kiranv_
Rhodochrosite | Level 12

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

6 REPLIES 6
Reeza
Super User

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
Quartz | Level 8
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
AnnaNZ
Quartz | Level 8

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
Super User

@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
Quartz | Level 8

 

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
kiranv_
Rhodochrosite | Level 12

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;


 

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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