DATA Step, Macro, Functions and more

Select most recent row with a certain value

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 90
Accepted Solution

Select most recent row with a certain value

[ Edited ]

I have a POLICY-table which triggers a new row (policy_version) each time a change is made to the policy.

 

I need a new column (WANT) which always displays the most recent policy version where the policy_code was either 15 or 70.

 

POLICY_ID POLICY_VERSION POLICY_CODE WANT
123456 1 0  
123456 2 0  
123456 3 0  
123456 4 70  
123456 5 0 4
123456 6 0 4
123456 7 0 4
123456 8 15 4
123456 9 0 8
123456 10 0 8
123456 11 0 8
123456 12 0 8
123456 13 0 8

 

Example: The last row (policy_version 13) sees that the most recent policy_version with policy_code 15 or 70 was policy_version 8.

 

Would appreciate help on this, thanks for your time.


Accepted Solutions
Solution
‎10-26-2016 09:20 AM
Super Contributor
Posts: 308

Re: Select most recent row with a certain value

Posted in reply to EinarRoed

Hello,

 

I guess you have more POLICY_IDies in the table so a prior sort may be needed. Afterwards the folllowing code will do it:

 

data want;
set have;
by POLICY_ID;

retain most_recent_policy;

if first.POLICY_ID then call missing(most_recent_policy);
if POLICY_CODE in (70, 15) then most_recent_policy=POLICY_VERSION;
run;

View solution in original post


All Replies
Super User
Super User
Posts: 7,988

Re: Select most recent row with a certain value

Posted in reply to EinarRoed

Sort the dataset descening, then the first encounter is the latest:

proc sort data=have;
  by policy_id descending policy_version;
run;
data want;
  set have;
  by policy_id;
  retain want;
  if first.policy_id then want=8;
  if policy=15 and want=8 then policy=4;
  if policy=70 and policy=4 then want=.;
run;
proc sort data=want;
  by policy_id policy_version;
run;
Solution
‎10-26-2016 09:20 AM
Super Contributor
Posts: 308

Re: Select most recent row with a certain value

Posted in reply to EinarRoed

Hello,

 

I guess you have more POLICY_IDies in the table so a prior sort may be needed. Afterwards the folllowing code will do it:

 

data want;
set have;
by POLICY_ID;

retain most_recent_policy;

if first.POLICY_ID then call missing(most_recent_policy);
if POLICY_CODE in (70, 15) then most_recent_policy=POLICY_VERSION;
run;
Super User
Posts: 5,516

Re: Select most recent row with a certain value

Posted in reply to EinarRoed

The WHERE statement makes this easy:

 

data want;

set have;

by policy_id policy_version;

where policy_code in (15, 70);

if last.policy_id;

run;

 

The WHERE statement sets up FIRST. and LAST. variables based on just the observations that meet the WHERE conditions.

☑ This topic is solved.

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

Discussion stats
  • 3 replies
  • 255 views
  • 3 likes
  • 4 in conversation