Hello,
I have a table that includes policies, with one row for each version of the policy. Each time a policy is changed, a new policy version is triggered. The policy versions are always in correct chronological order, however they can skip numbers, going from for example 5 straight to 7, and doesn't always start at 1.
I want a new column that always displays the previous policy version (per policy_id). When there is no previous policy version, then the first policy version should be displayed.
POLICY_ID | POLICY_VERSION | WANT |
1255 | 1 | 1 |
1255 | 2 | 1 |
1255 | 3 | 2 |
4567 | 1 | 1 |
4567 | 2 | 1 |
4567 | 5 | 2 |
4567 | 6 | 5 |
4567 | 8 | 6 |
9845 | 2 | 2 |
9845 | 3 | 2 |
9845 | 5 | 3 |
Would appreciate help with setting up this code. Thanks for your time!
data want;
set have;
by policy_id;
want = lag(policy_version);
if first.policy_id then want=policy_version;
run;
data want;
set have;
by policy_id;
want = lag(policy_version);
if first.policy_id then want=policy_version;
run;
To expand on the lag() function:
Every time it is called, it puts the current value of the named variable into it's FIFO queue, and yields the last value from that queue. If it is not called in a certain iteration of the data step, this action is not performed, and the current value is lost (never gets into the queue).
So it is usually a bad idea to call lag() in a conditional construct.
That is why I first use lag() unconditionally and then override with first.policy_id.
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: