Quartz | Level 8

## Retaining a flag value by id

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
1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Retaining a flag value by id

``````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.

Super User

## Re: Retaining a flag value by id

``````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.

Discussion stats