Retaining a flag value by id

[PetePatel]

Hi,

I have a dataset like shown below with ID, month and signal.

I need to create new flags signal_2 and signal_3 that effectively retains the value of 2 for 2 months (signal_2) or 3 months (signal_3), otherwise keeps the value in signal.

What is the most efficient way for me to code this by ID and month?

id	month	signal	signal_2	signal_3
1	Jan-20	0	0	0
1	Feb-20	0	0	0
1	Mar-20	1	1	1
1	Apr-20	1	1	1
1	May-20	2	2	2
1	Jun-20	2	2	2
1	Jul-20	3	2	2
1	Aug-20	3	2	2
1	Sep-20	3	3	2
1	Oct-20	3	3	3
1	Nov-20	3	3	3
1	Dec-20	4	4	4
2	Jun-20	0	0	0
2	Jul-20	4	4	4
2	Aug-20	4	4	4
2	Sep-20	4	4	4
2	Oct-20	2	2	2
2	Nov-20	0	2	2
2	Dec-20	1	2	2
2	Jan-21	1	1	2
2	Feb-21	2	2	2
2	Mar-21	4	2	2
2	Apr-21	4	2	2
2	May-21	4	4	2
2	Jun-21	1	1	1

[Kurt_Bremser · Accepted solution]

data have;
input id $ month :monyy6. signal;
format month yymmd7.;
datalines;
1 Jan-20  0
1 Feb-20  0
1 Mar-20  1
1 Apr-20  1
1 May-20  2
1 Jun-20  2
1 Jul-20  3
1 Aug-20  3
1 Sep-20  3
1 Oct-20  3
1 Nov-20  3
1 Dec-20  4
2 Jun-20  0
2 Jul-20  4
2 Aug-20  4
2 Sep-20  4
2 Oct-20  2
2 Nov-20  0
2 Dec-20  1
2 Jan-21  1
2 Feb-21  2
2 Mar-21  4
2 Apr-21  4
2 May-21  4
2 Jun-21  1
;

data want;
set have;
by id;
retain sig_count;
if first.id then sig_count = 0;
if signal = 2
then sig_count = 4;
else sig_count = max(0,sig_count - 1);
if sig_count in (3,2)
then signal_2 = 2;
else signal_2 = signal;
if sig_count ne 0
then signal_3 = 2;
else signal_3 = signal;
drop sig_count;
run;

proc print data=want noobs;
run;

Result:

id	month	signal	signal_2	signal_3
1	2020-01	0	0	0
1	2020-02	0	0	0
1	2020-03	1	1	1
1	2020-04	1	1	1
1	2020-05	2	2	2
1	2020-06	2	2	2
1	2020-07	3	2	2
1	2020-08	3	2	2
1	2020-09	3	3	2
1	2020-10	3	3	3
1	2020-11	3	3	3
1	2020-12	4	4	4
2	2020-06	0	0	0
2	2020-07	4	4	4
2	2020-08	4	4	4
2	2020-09	4	4	4
2	2020-10	2	2	2
2	2020-11	0	2	2
2	2020-12	1	2	2
2	2021-01	1	1	2
2	2021-02	2	2	2
2	2021-03	4	2	2
2	2021-04	4	2	2
2	2021-05	4	4	2
2	2021-06	1	1	1

Note how I presented source data in a data step with datalines; please do so yourself in the future, as it makes helping you much easier. Help us to help you.