topic Re: I want to flag two consecutive value that is > 1000 and three or more consecutive value > in SAS Programming

I want to flag two consecutive value that is > 1000 and three or more consecutive value > 1000

HUK-lkpbk-980 — Wed, 17 Nov 2021 18:28:53 GMT

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

Re: I want to flag two consecutive value that is > 1000 and three or more consecutive value >

ballardw — Wed, 17 Nov 2021 18:47:00 GMT

Please explain how you can have 3 years of consecutive values without having 2??? Your rule is incomplete.

Re: I want to flag two consecutive value that is > 1000 and three or more consecutive value >

sbxkoenk — Wed, 17 Nov 2021 18:53:48 GMT

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

Re: I want to flag two consecutive value that is > 1000 and three or more consecutive value >

HUK-lkpbk-980 — Wed, 17 Nov 2021 20:31:44 GMT

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.

Re: I want to flag two consecutive value that is > 1000 and three or more consecutive value >

sbxkoenk — Wed, 17 Nov 2021 22:07:39 GMT

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

Re: I want to flag two consecutive value that is > 1000 and three or more consecutive value >

HUK-lkpbk-980 — Wed, 17 Nov 2021 22:58:02 GMT

Thanks Koen, this is very close but I think I got it from here. Thank you so much for your help!!