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

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;

 

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

4 REPLIES 4
art297
Opal | Level 21

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

 

Art, CEO, AnalystFinder.com

 

SASKiwi
PROC Star

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.;
Haikuo
Onyx | Level 15

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

PGStats
Opal | Level 21

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

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
  • 4 replies
  • 1492 views
  • 0 likes
  • 5 in conversation