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

[HUK-lkpbk-980]

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

[ballardw]

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

[sbxkoenk]

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

[HUK-lkpbk-980]

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

[sbxkoenk · Accepted solution]

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

[HUK-lkpbk-980]

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