topic Re: Column that displays value from the previous row (per policy) in SAS Programming

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

EinarRoed — Tue, 11 Oct 2016 07:48:30 GMT

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!

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

Kurt_Bremser — Tue, 11 Oct 2016 07:53:50 GMT

data want;
set have;
by policy_id;
want = lag(policy_version);
if first.policy_id then want=policy_version;
run;

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

EinarRoed — Tue, 11 Oct 2016 08:02:16 GMT

Aha there's a lag function, so practical! Thanks, it gives me exactly what I want.

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

Kurt_Bremser — Tue, 11 Oct 2016 08:09:05 GMT

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.