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.
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.