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;
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;
It would help if you posted sample have and want datasteps (preferably in the form of data steps)
Art, CEO, AnalystFinder.com
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.;
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."
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.