topic Re: Select most recent row with a certain value in SAS Programming

Select most recent row with a certain value

EinarRoed — Wed, 26 Oct 2016 12:40:48 GMT

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.

Re: Select most recent row with a certain value

RW9 — Wed, 26 Oct 2016 12:46:52 GMT

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;

Re: Select most recent row with a certain value

Loko — Wed, 26 Oct 2016 12:47:32 GMT

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;

Re: Select most recent row with a certain value

Astounding — Wed, 26 Oct 2016 13:14:24 GMT

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.