I am very new to SAS. If I have a dataset that contains IDs, Year, and Revenue and I want to create two flags, one with ID that has two consecutive years with revenue >= 1000 and one with ID that has three or more consecutive years with revenue >= 1000. How do I create flag2 and flag3orMore. Thanks so much
Example:
ID year Revenue Flag2 Flag3OrMore
1 2015 1227 0 1
1 2016 1126 0 1
1 2017 2153 0 1
1 2018 936 0 0
1 2019 1325 0 0
2 2018 223 0 0
2 2019 456 0 0
2 2020 789 0 0
3 2019 1236 1 0
3 2020 1199 1 0
4 2016 1356 1 0
4 2017 1212 1 0
4 2018 232 0 0
4 2019 1125 0 1
4 2020 1214 0 1
4 2021 1145 0 1
Hello,
If I understand your question well, this should be the answer to your question:
data work.have;
input ID $ year revenue;
datalines;
1 2015 999
1 2016 1001
1 2017 989
1 2018 1000
1 2019 1230
1 2020 1425
1 2021 995
;
run;
data work.want;
set work.have;
by ID;
retain WANTED_RET_CONSYEARS;
if first.ID then WANTED_RET_CONSYEARS=0;
if revenue >= 1000 then WANTED_RET_CONSYEARS = WANTED_RET_CONSYEARS +1;
else do;
if WANTED_RET_CONSYEARS >= 3 then WANTED_RET_CONSYEARS = WANTED_RET_CONSYEARS;
else WANTED_RET_CONSYEARS = 0;
end;
run;
/* end of program */
Thanks,
Koen
Please explain how you can have 3 years of consecutive values without having 2??? Your rule is incomplete.
Hello,
Something like this?
data work.have;
input ID $ year revenue;
datalines;
1 2015 1227
1 2016 1126
1 2017 2153
1 2018 936
1 2019 1325
2 2018 223
2 2019 456
2 2020 789
3 2019 1236
3 2020 1199
4 2016 1356
4 2017 1212
4 2018 232
4 2019 1125
4 2020 1214
4 2021 1145
;
run;
proc sort data=work.have;
by ID year;
run;
data work.want(drop=ret_consyears);
set work.have;
by ID year;
Flag2=0;
Flag3OrMore=0;
retain ret_consyears;
if first.ID then ret_consyears=0;
if revenue > 1000 then ret_consyears = ret_consyears + 1;
if last.ID then do;
if ret_consyears >=3 then Flag3OrMore=1;
else if ret_consyears =2 then Flag2 =1;
else;
output;
end;
run;
/* end of program */
Koen
Hi Koen,
This gets very close to what I want, however, using your code, the ret_consyears in following example wasn't calculated correctly.
Id Year Revenue flag2 flag3More Ret_consyears WANTED_RET_CONSYEARS
1 2015 999 0 0 0 0
1 2016 1001 0 0 1 1
1 2017 989 0 0 1 0
1 2018 1000 0 0 2 1
1 2019 1230 0 0 3 2
1 2020 1425 0 0 4 3
1 2021 995 0 1 4 3
Do you think it is possible? I only the want # consecutive years that met threshold. So in the above example, that ID retained for 3 consecutive years.
1
Hello,
If I understand your question well, this should be the answer to your question:
data work.have;
input ID $ year revenue;
datalines;
1 2015 999
1 2016 1001
1 2017 989
1 2018 1000
1 2019 1230
1 2020 1425
1 2021 995
;
run;
data work.want;
set work.have;
by ID;
retain WANTED_RET_CONSYEARS;
if first.ID then WANTED_RET_CONSYEARS=0;
if revenue >= 1000 then WANTED_RET_CONSYEARS = WANTED_RET_CONSYEARS +1;
else do;
if WANTED_RET_CONSYEARS >= 3 then WANTED_RET_CONSYEARS = WANTED_RET_CONSYEARS;
else WANTED_RET_CONSYEARS = 0;
end;
run;
/* end of program */
Thanks,
Koen
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.