Hello,
This is a Have data and I want to generate Flag variable as seen in Wanted?
Have:
Date | Account ID | Flag |
4/30/2022 | 1 | 0 |
5/31/2022 | 1 | 0 |
6/30/2022 | 1 | 0 |
7/31/2022 | 1 | 1 |
8/31/2022 | 1 | 0 |
9/30/2022 | 1 | 0 |
Want:
Date | Account ID | Flag |
4/30/2022 | 1 | 0 |
5/31/2022 | 1 | 0 |
6/30/2022 | 1 | 0 |
7/31/2022 | 1 | 1 |
8/31/2022 | 1 | 2 |
9/30/2022 | 1 | 3 |
Just use a SUM statement and the fact that SAS evaluates Boolean expressions to 1 for TRUE and 0 for FALSE.
data want;
set have;
by account date ;
if first.account then new_flag=0;
new_flag + (flag or new_flag);
run;
Result:
OBS account Date Flag new_flag 1 1 2022-04-30 0 0 2 1 2022-05-31 0 0 3 1 2022-06-30 0 0 4 1 2022-07-31 1 1 5 1 2022-08-31 0 2 6 1 2022-09-30 0 3
Note: You need to create a NEW variable for the RETAIN to work. If you try use a variable that is already on the input dataset then the retained value is lost when the next observation is read in.
Just use a SUM statement and the fact that SAS evaluates Boolean expressions to 1 for TRUE and 0 for FALSE.
data want;
set have;
by account date ;
if first.account then new_flag=0;
new_flag + (flag or new_flag);
run;
Result:
OBS account Date Flag new_flag 1 1 2022-04-30 0 0 2 1 2022-05-31 0 0 3 1 2022-06-30 0 0 4 1 2022-07-31 1 1 5 1 2022-08-31 0 2 6 1 2022-09-30 0 3
Note: You need to create a NEW variable for the RETAIN to work. If you try use a variable that is already on the input dataset then the retained value is lost when the next observation is read in.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.