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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.