Select most recent row with a certain value

[EinarRoed]

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.

[RW9]

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;

[Loko · Accepted solution]

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;

[Astounding]

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.