topic Re: retain within different values in SAS Programming

retain within different values

podarum — Fri, 09 Dec 2016 02:32:52 GMT

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.

Re: retain within different values

Astounding — Fri, 09 Dec 2016 02:45:28 GMT

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;

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

Re: retain within different values

ChrisNZ — Fri, 09 Dec 2016 03:05:03 GMT

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

Re: retain within different values

Ksharp — Fri, 09 Dec 2016 03:25:50 GMT

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;

Re: retain within different values

podarum — Fri, 09 Dec 2016 14:14:03 GMT

Thank you, this is perfect...