DATA Step, Macro, Functions and more

retain within different values

Accepted Solution Solved
Reply
Super Contributor
Posts: 395
Accepted Solution

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.

 

Thanks in advance.


Accepted Solutions
Solution
‎12-09-2016 09:14 AM
Super User
Posts: 5,072

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

View solution in original post


All Replies
Solution
‎12-09-2016 09:14 AM
Super User
Posts: 5,072

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: 395

Re: retain within different values

Thank you, this is perfect...

PROC Star
Posts: 1,558

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: 9,662

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 236 views
  • 2 likes
  • 4 in conversation