Applyig loop and create a new variable

[fama]

Hi all,

data have;
input ID Priod Policy_Period amount ;
cards;
1 1 2 100
1 2 2 200
1 3 2 500
1 4 2 200
2 1 3 350
2 2 3 480
2 3 3 10
2 4 3 0
3 1 4 145
3 2 4 150
3 3 4 300
3 4 4 200
;;;;
run;

I want to find the amount for the period before the
policy-period and the amount after the policy period for each ID.

data want;
input ID amount_before amount_after;
cards;
1 100 500
2 480 0
3 300 .
;;;;
run;

[PeterClemmensen · Accepted solution]

data want(keep=ID amount_before amount_after);
    call missing(ID, amount_before, amount_after);
    do until (last.ID);
        set have;
        by ID;
        if Priod-Policy_Period = -1 then amount_before = amount;
        if Priod-Policy_Period =  1  then amount_after  = amount;
    end;
run;

Result:

ID     amount_before  amount_after
1      100            500
2      480            0
3      300            .

[fama]

Hi draycut,

Thanks for the code.
It works well. However, not for my actual data…
The IDs in my actual data are not ordered as 1,2,3..that I showed here. They are 8 digit randomised numbers like 30149326,12365478 etc..
Is it the issue does not let the code work well?
Appreciate your time

[PeterClemmensen]

Ok. But Policy_Period is still constant for each id? and in one of the obs, Policy_Period=Period, correct?

[fama]

Hi again draycut,

it was my mistake,,,
all good.
thanks again