How to retain the value at each flag occurrence for each unique account?

[sas_newbie94]

Hi All,

Would require assistance to achieve the following new amount value to be inputted based on a flag and the specified amount tied to it.

Please find screenshot for reference. For each account number, I would like to RETAIN the first amount at each occurrence of flag and affect subsequent records below. Once there's a new amount, it will take in the new amount and bring it down to the subsequent rows below. This behavior applies to the same account number until it reaches a new account number. 

data example;
input accno flag amount ;
datalines;
100 . .
100 . .
100 . .
100 . .
100 1 1000
100 1 .
100 1 .
100 1 .
100 1 3000
100 1 .

200 . .
200 . .
200 . .
200 . .
200 . .
200 1 500
200 1 .
200 1 .
200 1 700
200 1 .
;

run;

[ballardw · Accepted solution]

One way might be:

data want; 
   set example;
   by accno;
   retain new_amount;
   if first.accno then new_amount=.;
   if flag and amount then new_amount=amount;
   if missing(flag) then new_amount=.;
run;

This works for the provided example.

The " if flag and amount then new_amount=amount;" works if flag and amount are not 0 as SAS treats non-zero non-missing numeric values as "true". If zero is a possible valid value for either then use

if not missing(flag) and not missing(amount) then new_amount=amount;

You did not show if the Flag value might ever be missing after it was set within an Accno group. I am guessing that you likely would want to reset the new_amount to missing if that every occurs.