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

From the data mentioned below, I want to generate id’s that had a flag=1  for two or more consecutive months. As described below, some id's had a flag=1 for 12 consecutive months, only 6 consecutive months, 4 consecutive months, and finally only a month. Also some id's had a flag=1 but not consecutively, for instance at month 12 of 2013 and month 2 of 2014. The output I want is only those ID’s who had a flag=1 for two or more consecutive months.

I really appreciate your help.

Id   year   month flag

1   2013    7        1

1   2013    8        1

1   2013     9        1

1   2013     10       1

1 2013     11        1

1 2013       12        1

1 2014       1          1

1 2014       2          1

1 2014       3          1

1 2014         4        1

1 2014         5          1

1 2014         6          1

2   2013      8        1

2   2013      9        1

2   2013      10       1

2 2013     11        1

2 2013       12        1

2 2014       1          1

3 2014       2          1

3 2014       3          1

3 2014       4          1

3 2014         5          1

4 2013       12          1

4 2014       1          1

5 2013       9          1

5 2014       3        1

6 2013       12          1

6 2014     2         1

7 2014    2          1

8 2013    7          1

8 2013    8          1

Output

Id

1

2

3

4

8

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

Make use of the fine SAS date functions by converting your time data to SAS date values, and use the lag() function:

data have;
input id year month flag;
cards;
1   2013    7        1
1   2013    8        1
1   2013     9        1
1   2013     10       1
1 2013     11        1
1 2013       12        1
1 2014       1          1
1 2014       2          1
1 2014       3          1
1 2014         4        1
1 2014         5          1
1 2014         6          1
2   2013      8        1
2   2013      9        1
2   2013      10       1
2 2013     11        1
2 2013       12        1
2 2014       1          1
3 2014       2          1
3 2014       3          1
3 2014       4          1
3 2014         5          1
4 2013       12          1
4 2014       1          1
5 2013       9          1
5 2014       3        1
6 2013       12          1
6 2014     2         1
7 2014    2          1
8 2013    7          1
8 2013    8          1
;
run;

/* first, let's create SAS date values from year and month */
data int;
set have;
if flag = 1; * just to be sure;
period = mdy(month,1,year);
keep id period;
run;

/* now check if two observations within a group are only 1 month apart */
data want (keep=id);
set int;
by id period; * period to make sure that dataset is correctly ordered;
if not first.id and intck('month',lag(period),period) = 1; * this is why we created SAS date values;
run;

/* remove duplicates */
proc sort data=want nodupkey;
by id;
run;

Note how I presented your data in a data step; this enables everyone reading the post to recreate the dataset with a simple copy/paste and submit, no tedious typing from screen or reformatting needed. And it makes sure that the dataset hase the intended structure.

Also note how code looks when using the correct posting sub-window ("little running man" or {i} buttons).

 

 

Edit: fixed typo ("data functions" -> "date functions" in first line)

View solution in original post

11 REPLIES 11
PeterClemmensen
Tourmaline | Level 20

Something like this

 

data have;
input Id$ year month flag;
datalines;
1 2013 7 1
1 2013 8 1
1 2013 9 1
1 2013 10 1
1 2013 11 1
1 2013 12 1
1 2014 1 1
1 2014 2 1
1 2014 3 1
1 2014 4 1
1 2014 5 1
1 2014 6 1
2 2013 8 1
2 2013 9 1
2 2013 10 1
2 2013 11 1
2 2013 12 1
2 2014 1 1
3 2014 2 1
3 2014 3 1
3 2014 4 1
3 2014 5 1
4 2013 12 1
4 2014 1 1
5 2013 9 1
5 2014 3 1
6 2013 12 1
6 2014 2 1
7 2014 2 1
8 2013 7 1
8 2013 8 1
;

proc sort data=have;
	by ID year month;
run;

data want(keep=ID);
	set have;
	by ID year month;

	dif=dif(month);
	if first.ID then outflag=0;
	else if flag=1 and dif in(1,-11) and outflag=0 then outflag=1;

	retain outflag 0;
	if last.ID and outflag=1;
run;
Abimal_Zippi
Fluorite | Level 6

That is true, the main challenge to me was to make sure those who had the flag between month 12 and 1 are included. I will take care of this note. Thank you so much

Kurt_Bremser
Super User

@tesfuc wrote:

That is true, the main challenge to me was to make sure those who had the flag between month 12 and 1 are included. I will take care of this note. Thank you so much


That is why I recommend to use SAS date/datetime/time values wherever possible. As soon as you have them, usage of intck/intnx takes care of all the quirks of the date and time measuring systems (irregular days/month, non-decimal fractional notations like 60sec/min or 12months/year)

PeterClemmensen
Tourmaline | Level 20

@Kurt_Bremser ah yes, did not think of that 🙂 Right as usual.

 

@Abimal_Zippi thank you, but I recommend that you go with Kurts solution

Kurt_Bremser
Super User

Make use of the fine SAS date functions by converting your time data to SAS date values, and use the lag() function:

data have;
input id year month flag;
cards;
1   2013    7        1
1   2013    8        1
1   2013     9        1
1   2013     10       1
1 2013     11        1
1 2013       12        1
1 2014       1          1
1 2014       2          1
1 2014       3          1
1 2014         4        1
1 2014         5          1
1 2014         6          1
2   2013      8        1
2   2013      9        1
2   2013      10       1
2 2013     11        1
2 2013       12        1
2 2014       1          1
3 2014       2          1
3 2014       3          1
3 2014       4          1
3 2014         5          1
4 2013       12          1
4 2014       1          1
5 2013       9          1
5 2014       3        1
6 2013       12          1
6 2014     2         1
7 2014    2          1
8 2013    7          1
8 2013    8          1
;
run;

/* first, let's create SAS date values from year and month */
data int;
set have;
if flag = 1; * just to be sure;
period = mdy(month,1,year);
keep id period;
run;

/* now check if two observations within a group are only 1 month apart */
data want (keep=id);
set int;
by id period; * period to make sure that dataset is correctly ordered;
if not first.id and intck('month',lag(period),period) = 1; * this is why we created SAS date values;
run;

/* remove duplicates */
proc sort data=want nodupkey;
by id;
run;

Note how I presented your data in a data step; this enables everyone reading the post to recreate the dataset with a simple copy/paste and submit, no tedious typing from screen or reformatting needed. And it makes sure that the dataset hase the intended structure.

Also note how code looks when using the correct posting sub-window ("little running man" or {i} buttons).

 

 

Edit: fixed typo ("data functions" -> "date functions" in first line)

Abimal_Zippi
Fluorite | Level 6

Dear KurtBremser

I would like to try a different scenario, i.e. if there is a flag for three or more consecutive months, what changes should I made in the code.

From the above dataset, the desired output will be -

Id

1

2

3

Because all have flag=1 for three or more consecutive months.

I really appreciate your help again.

Kurt_Bremser
Super User

While the lag() functions keeps a 1-stage FIFO chain, the lag2() function keeps 2 stages. With it you can "look back" for 2 observations.

When you now retain a counter variable that is set to 1 at first.id and incremented otherwise, you can expand my condition to check for 2 observations back when counter > 2.

Abimal_Zippi
Fluorite | Level 6

Thank you so much,

I just used this code but couldn't get with lag2() function...

data want_2 (keep=id);

 

set int;

by id period;

if first.id then cnt=0;

if not first.id and intck('month',lag(period),period)= 1 then cnt+1;

retain cnt 0;

if cnt ge 2;

run;

Kurt_Bremser
Super User

Don't think so complicated:

data want (keep=id);
set int;
by id period;
retain counter;
if first.id
then counter = 1;
else counter + 1;
if counter > 2 and intck('month',lag(period),period) = 1 and intck('month',lag2(period),period) = 2;
run;

Call this

retain counter;
if first.id
then counter = 1;
else counter + 1;

a "counter block"; you will use if often when doing by-group processing in a data step.

Abimal_Zippi
Fluorite | Level 6

I really appreciate your help. That is perfect.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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
  • 11 replies
  • 4413 views
  • 4 likes
  • 3 in conversation