DATA Step, Macro, Functions and more

Pulling Data for Any Three Consecutive Months

Accepted Solution Solved
Reply
Occasional Contributor thb
Occasional Contributor
Posts: 7
Accepted Solution

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
Respected Advisor
Posts: 4,646

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

View solution in original post


All Replies
PROC Star
Posts: 7,363

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: 3,105

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.;
Respected Advisor
Posts: 3,124

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
Respected Advisor
Posts: 4,646

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 126 views
  • 0 likes
  • 5 in conversation