## retain within different values

Solved
Super Contributor
Posts: 409

# retain within different values

[ Edited ]

Hi,  I'm tryin got retain a specific value wihtin a group of accounts but the selector can change within the sequence.  I want only the last one.   Here's more detail:

HAVE

acct    status     date     bal

001     PD0         1        234

001     PD1         2        230

001     PD1         3        220

001     PD2         4        220

001     PD1         5        210

001     PD2         6        200

001     PD3         7        200

001     PD4         8        200

003     PD0         1        567

002     PD0         1        234

002     PD0         2        966

002     PD1         3        966

002     PD1         4        950

002     PD0         5        900

002     PD0         6        850

002     PD1         7        850

002     PD2         8        840

004     PD0         1        230

I would like only 1 row per account that shows the last bal value for each PD1 and PD2...and in the status the worse or last possible PD for each account.

WANT

acct    status     date     bal     PD1_bal    PD1_ind     PD2_bal     PD2 _ind

001      PD4        8       200         210             1                200              1

002      PD2        8       840         850             1                840              1

003      PD0        1       567          0                0                  0                0

004      PD0        1       230          0                0                  0                0

the Pd_ind fields are there as counters.  I'm trying to create a Bad Rate indicator for a PD1 and another for PD2 and another for whatever is the worse.  Also note that this example is small, but the actual data is very large, so I'd have to use something like retain and last. , I presume.

Accepted Solutions
Solution
‎12-09-2016 09:14 AM
Super User
Posts: 6,927

## Re: retain within different values

The very last part of your question about a Bad Rate indicator isn't clear, but this should get the results that you illustrated.

data want;

PD1_bal=0;

PD2_bal=0;

PD1_ind=0;

PD2_ind=0;

do until (last.acct);

set have;

by acct notsorted;

if status='PD1' then do;

PD1_bal = bal;

PD1_ind + 1;

end;

else if status='PD2' then do;

PD2_bal = bal;

PD2_ind + 1;

end;

end;

run;

I added "notsorted" only because you have acct 003 before acct 002.  If they are actually in order in the real data, you can remove "notsorted".

All Replies
Solution
‎12-09-2016 09:14 AM
Super User
Posts: 6,927

## Re: retain within different values

The very last part of your question about a Bad Rate indicator isn't clear, but this should get the results that you illustrated.

data want;

PD1_bal=0;

PD2_bal=0;

PD1_ind=0;

PD2_ind=0;

do until (last.acct);

set have;

by acct notsorted;

if status='PD1' then do;

PD1_bal = bal;

PD1_ind + 1;

end;

else if status='PD2' then do;

PD2_bal = bal;

PD2_ind + 1;

end;

end;

run;

I added "notsorted" only because you have acct 003 before acct 002.  If they are actually in order in the real data, you can remove "notsorted".

Super Contributor
Posts: 409

## Re: retain within different values

Thank you, this is perfect...

Super User
Posts: 2,505

## Re: retain within different values

[ Edited ]

No too sure what you are counting, but you should be able to add this detail to the following:

``````data HAVE;
input (ACCT    STATUS     DATE     BAL) (: \$4.);
cards;
001     PD0         1        234
001     PD1         2        230
001     PD1         3        220
001     PD2         4        220
001     PD1         5        210
001     PD2         6        200
001     PD3         7        200
001     PD4         8        200
002     PD0         1        234
002     PD0         2        966
002     PD1         3        966
002     PD1         4        950
002     PD0         5        900
002     PD0         6        850
002     PD1         7        850
002     PD2         8        840
run;
data WANT;
set HAVE;
by ACCT;
array PD_BAL [0:4] \$4 PD_BAL0-PD_BAL4;
retain PD_BAL:;
if first.ACCT then call missing(of PD_BAL[*]);
PD_BAL[compress(STATUS,,'a')]=BAL;
if last.ACCT then output;
keep ACCT STATUS DATE PD_BAL:;
run;
``````

SAS Output

ACCT STATUS DATE PD_BAL0 PD_BAL1 PD_BAL2 PD_BAL3 PD_BAL4
001 PD4 8 234 210 200 200 200
002 PD2 8 850 850 840

Super User
Posts: 10,849

## Re: retain within different values

``````data have;
input acct \$   status  \$   date     bal;
cards;
001     PD0         1        234
001     PD1         2        230
001     PD1         3        220
001     PD2         4        220
001     PD1         5        210
001     PD2         6        200
001     PD3         7        200
001     PD4         8        200
003     PD0         1        567
002     PD0         1        234
002     PD0         2        966
002     PD1         3        966
002     PD1         4        950
002     PD0         5        900
002     PD0         6        850
002     PD1         7        850
002     PD2         8        840
004     PD0         1        230
;
run;
data want;
PD1_bal=0;    PD1_ind=0;    PD2_bal=0;     PD2_ind=0;
do until(last.acct);
set have;
by acct notsorted;
if status='PD1' then do;PD1_bal=bal;PD1_ind=1;end;
else if status='PD2' then do;PD2_bal=bal;PD2_ind=1;end;
end;
run;``````
☑ This topic is solved.