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.
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;
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;
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;
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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.