BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
PetePatel
Quartz | Level 8

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?

 

idmonthsignalsignal_2signal_3
1Jan-20000
1Feb-20000
1Mar-20111
1Apr-20111
1May-20222
1Jun-20222
1Jul-20322
1Aug-20322
1Sep-20332
1Oct-20333
1Nov-20333
1Dec-20444
2Jun-20000
2Jul-20444
2Aug-20444
2Sep-20444
2Oct-20222
2Nov-20022
2Dec-20122
2Jan-21112
2Feb-21222
2Mar-21422
2Apr-21422
2May-21442
2Jun-21111
1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User
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.

View solution in original post

1 REPLY 1
Kurt_Bremser
Super User
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.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • 372 views
  • 1 like
  • 2 in conversation