BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
fama
Fluorite | Level 6


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;

1 ACCEPTED SOLUTION

Accepted Solutions
PeterClemmensen
Tourmaline | Level 20
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            .

 

View solution in original post

4 REPLIES 4
PeterClemmensen
Tourmaline | Level 20
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
Fluorite | Level 6
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
Tourmaline | Level 20

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

fama
Fluorite | Level 6
Hi again draycut,

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

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 1141 views
  • 0 likes
  • 2 in conversation