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;
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.