## Pulling Data for Any Three Consecutive Months

Solved
Occasional Contributor
Posts: 7

# Pulling Data for Any Three Consecutive Months

[ Edited ]

Can anyone help with the following - I am trying to pull data for any consecutive three months for a performance period between October through Feb. Currently, the data pulls for Oct/Nov/Dec OR Nov/Dec/Jan, but not sure how to write the code to pull for Oct/Nov/Dec or Nov/Dec/Jan or Dec/Jan/Feb. Thank you for your help in advance.

```*** Create test data ***;
data Test1;
*** HCO with all four months ***;
HCO = 1; Year = 2016; Month = 10; output;
HCO = 1; Year = 2016; Month = 11; output;
HCO = 1; Year = 2016; Month = 12; output;
HCO = 1; Year = 2017; Month = 1; output;

*** HCO with three consecutive months ***;
HCO = 2; Year = 2016; Month = 11; output;
HCO = 2; Year = 2016; Month = 12; output;
HCO = 2; Year = 2017; Month = 1; output;

*** HCO with three consecutive months ***;
HCO = 3; Year = 2016; Month = 10; output;
HCO = 3; Year = 2016; Month = 11; output;
HCO = 3; Year = 2016; Month = 12; output;

*** HCO with gap ***;
HCO = 4; Year = 2016; Month = 10; output;
HCO = 4; Year = 2016; Month = 12; output;
HCO = 4; Year = 2017; Month = 1; output;
run;

proc sort data=Test1;
by HCO Year Month;
run;

*** Create a list of all months ***;
proc sql;
create table Months1 as
select distinct Year, Month
from Test1
order by Year, Month;
quit;

*** Create a list of HCOs ***;
proc sql;
create table HCO1 as
select distinct HCO
from Test1
order by HCO;
quit;

*** Create combinations of all possible HCOs/Years/Months ***;
proc sql;
create table Master1 as
select a.*, b.*
from HCO1 a,
Months1 b
order by HCO, Year, Month;
quit;

*** Flag months with data ***;
data Check1;
merge Master1 (in=ina)
Test1 (in=inb);
by HCO Year Month;

if inb then
Flag = 1;   *** We have data ***;
else
Flag = 0;   *** We do not have data ***;
run;

*** Check for three consecutive months ***;
data Check2 (keep=HCO);
set Check1;
by HCO Year Month;

retain Consecutive;

if first.HCO then
Consecutive = 0;

if Flag = 1 then
Consecutive = Consecutive + 1;
else
Consecutive = 0;

if Consecutive = 3 then
output;
run;

data Test2;
merge Test1 (in=ina)
Check2 (in=inb);
by HCO;

if ina and inb;
run;

data Months2;
Year = 2016; Month = 11; output;
Year = 2016; Month = 12; output;
Year = 2017; Month = 1; output;
run;

proc sort data=Test2;
by Year Month HCO;
run;

data Test3;
merge Test2 (in=ina)
Months2 (in=inb);
by Year Month;

if inb;
run;

proc sort data=Test3;
by HCO Year Month;
run;

proc sql;
create table Three1 as
select HCO,
count(*) as Months
from Test3
group by HCO
having count(*) = 3
order by HCO;
quit;```

Accepted Solutions
Solution
‎05-08-2017 01:15 PM
Posts: 5,624

## Re: Pulling Data for Any Three Consecutive Months

``````
data test2;
set test1;
monthDate = mdy(month,1,year);
drop month year;
format monthDate yymm.;
run;

proc sql;
create table test3 as
select
t1.hco,
t1.monthDate,
t2.monthDate as monthDate2,
t3.monthDate as monthDate3
from
test2 as t1 inner join
test2 as t2 on t1.hco=t2.hco and intnx("month", t1.monthDate, 1) = t2.monthDate inner join
test2 as t3 on t1.hco=t3.hco and intnx("month", t1.monthDate, 2) = t3.monthDate;
select * from test3;
quit;``````
PG

All Replies
Super User
Posts: 8,216

## Re: Pulling Data for Any Three Consecutive Months

It would help if you posted sample have and want datasteps (preferably in the form of data steps)

Art, CEO, AnalystFinder.com

Super User
Posts: 4,019

## Re: Pulling Data for Any Three Consecutive Months

You are making life hard for yourself by storing year and month in separate variables. Your task would be a lot easier if you created a SAS date for your data:

``````date = mdy(month, 1, year);
format date monyy7.;``````
Posts: 3,185

## Re: Pulling Data for Any Three Consecutive Months

To elaborate what @art297 just said, if 'test1' is your incoming data, please post your desired outcome. Your last table 'three1' probably is not what you desired outcome, otherwise, you could just live with what you currently have. Your description doesn't help much, as far as I can read, the following statements is very confusing to me:

"Currently, the data pulls for Oct/Nov/Dec OR Nov/Dec/Jan, but not sure how to write the code to pull for
Oct/Nov/Dec or Nov/Dec/Jan or Dec/Jan/Feb."

Solution
‎05-08-2017 01:15 PM
Posts: 5,624

## Re: Pulling Data for Any Three Consecutive Months

``````
data test2;
set test1;
monthDate = mdy(month,1,year);
drop month year;
format monthDate yymm.;
run;

proc sql;
create table test3 as
select
t1.hco,
t1.monthDate,
t2.monthDate as monthDate2,
t3.monthDate as monthDate3
from
test2 as t1 inner join
test2 as t2 on t1.hco=t2.hco and intnx("month", t1.monthDate, 1) = t2.monthDate inner join
test2 as t3 on t1.hco=t3.hco and intnx("month", t1.monthDate, 2) = t3.monthDate;
select * from test3;
quit;``````
PG
☑ This topic is solved.