topic Re: Pulling Data for Any Three Consecutive Months in SAS Programming

Pulling Data for Any Three Consecutive Months

thb — Thu, 27 Apr 2017 21:04:59 GMT

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;

Re: Pulling Data for Any Three Consecutive Months

art297 — Thu, 27 Apr 2017 19:58:41 GMT

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

Art, CEO, AnalystFinder.com

Re: Pulling Data for Any Three Consecutive Months

SASKiwi — Thu, 27 Apr 2017 20:29:03 GMT

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.;

Re: Pulling Data for Any Three Consecutive Months

Haikuo — Thu, 27 Apr 2017 20:43:54 GMT

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."

Re: Pulling Data for Any Three Consecutive Months

PGStats — Thu, 27 Apr 2017 22:12:49 GMT


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;