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 |
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.
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.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.
Ready to level-up your skills? Choose your own adventure.