Column that displays value from the previous row (per policy)

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 90
Accepted Solution

Column that displays value from the previous row (per policy)

[ Edited ]

 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!


Accepted Solutions
Solution
‎10-11-2016 04:01 AM
Super User
Posts: 7,854

Re: Column that displays value from the previous row (per policy)

Posted in reply to EinarRoed
data want;
set have;
by policy_id;
want = lag(policy_version);
if first.policy_id then want=policy_version;
run;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Solution
‎10-11-2016 04:01 AM
Super User
Posts: 7,854

Re: Column that displays value from the previous row (per policy)

Posted in reply to EinarRoed
data want;
set have;
by policy_id;
want = lag(policy_version);
if first.policy_id then want=policy_version;
run;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 90

Re: Column that displays value from the previous row (per policy)

Posted in reply to KurtBremser
Aha there's a lag function, so practical! Thanks, it gives me exactly what I want.
Super User
Posts: 7,854

Re: Column that displays value from the previous row (per policy)

Posted in reply to EinarRoed

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 308 views
  • 2 likes
  • 2 in conversation