retain within different values

[podarum]

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.

Thanks in advance.

[Astounding · Accepted solution]

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".

[podarum]

Thank you, this is perfect...

[ChrisNZ]

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

[Ksharp]

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;