macro sql loop - from current month generate the next 3 months

[sufiya]

NEED HELP asap very new to SAS - trying to get the next 3 months to display from current month.

i.e. if current month is September then the next 3 months will be October, November and December but all I was able to find online was how to generate the past 3 months - how can I using the same logic get future months? 

/*** the start data and end data of the month reported*****/
%let cmbtd='01SEP2017'd;
%let cmetd='30SEP2017'd;

%let repMthCM=09;            /***current month**********/
%let repYrCM=2017;

%let startRep1='2017-09-01 00:00:00';
%let ENDRep1='2017-09-30 00:00:00';


%let startRep2='2017-12-01 00:00:00';
%let ENDRep2='2017-12-31 00:00:00';


/**************************************************************/


/****Loop: ***i=1=current month , i=2 Next Month, i=3 Next next Month, i=4 Next next next month****/

%macro sqlloop; 
       PROC SQL; 
         %DO i=1 %TO 4;         
            %if &repMthCM-&i<1 %then %do;
                  %let repMth=12+&repMthCM-&i ;
                   %let repYr=&repYrCM-1;
            %end;

            %else %do;
                    %let repMth=&repMthCM-&i ;
                    %let repYr=&repYrCM;
            %end;          
    
 
PROC SQL;
CONNECT TO TERADATA(USER="USERI" PASSWORD="PASSWORD" SERVER="X" mode=teradata 
DATABASE="X");    

CREATE TABLE  B_CalendarCycleData&i AS
SELECT 
a.TRANSACTION_TYPE,
a.FEATURE_CODE,
put(a.CYCLE_CODE, 9.0) as CYCLE_CODE,
put(day(DATEPART(a.Cycle_Start_Date)), z2.) as Start_Date_IND,
put(day(DATEPART(a.Cycle_Start_Date)), z2.) as Cycle_Start_Date,
a.Cycle_Start_Date as Cycle_Start_Day,
a.JOURNALIZATION_DATE,
put(a.cycle_run_year, 4.)||'-'||(case when a.cycle_run_month<10 then '0'||put(a.cycle_run_month, 1.0)
                                else put(a.cycle_run_month, 2.0) end ) as cycle_run_YR_MTH,
a.cycle_run_month,
a.cycle_run_year,
e1.gl_DESC1,

datepart(a.cycle_start_date) as Cycle_Start_DateO format = date9.,
DATEPART(a.Cycle_End_Date) AS cycle_END_dateO FORMAT = DATE9.,

(case when a.cycle_run_month=12 then put(a.cycle_run_year+1, 4.) else put(a.cycle_run_year, 4.) end )
    ||'-'||(case when a.cycle_run_month=12 then '01'
                 when a.cycle_run_month<9 then '0'||put(a.cycle_run_month+1, 1.0)
                    else put(a.cycle_run_month+1, 2.0) end ) as repMTH 

/***
put(a.cycle_run_year, 4.)||'-'||(case when a.cycle_run_month<9 then '0'||put(a.cycle_run_month+1, 1.0)
                                else put(a.cycle_run_month+1, 2.0) end ) as repMTH ***/

FROM CONNECTION TO TERADATA(
select 
TRANSACTION_TYPE,
FEATURE_CODE,
CYCLE_CODE,
Cycle_Start_Date,
TELCO,
JOURNALIZATION_DATE,
cycle_run_month,
cycle_run_year,
cycle_start_date,
Cycle_End_Date


from GRP_ENH_VW
where CYCLE_CODE in (6, 23, 24, 31)
and cycle_run_month =&repMth  /***The Marco proess has make the prior month for caledear cycle****/
and cycle_run_year =&repYr
) as A

left join lm_gl1 e1 on a.TRANSACTION_TYPE = e1.TRANSACTION_TYPE 

DISCONNECT FROM TERADATA;

%END; 
QUIT;
%mend; 
%sqlloop;

[Reeza]

            %if &repMthCM-&i<1 %then %do;
                  %let repMth=12+&repMthCM-&i ;
                   %let repYr=&repYrCM-1;
            %end;

            %else %do;
                    %let repMth=&repMthCM-&i ;
                    %let repYr=&repYrCM;
            %end;     

In your code, what do you think happens when the month is 12? It switches to 13 which isn't valid I suspect.

Did you look at the SAS macro appendix to loop over dates? It's exactly what you want.

You need to use INTNX to increment your dates otherwise you  have to deal with issues like crossing the year boundaries.

And do you have to loop this? 

[sufiya]

Hi @Reeza,  When I do that the query returns with no errors but 0 rows/results. 

Yes - want to use the same logic and reverse it to get the next following 3 months. 

thank you for your help!  

[Patrick]

@sufiya Well, do these future months exist in your Teradata source table?

[sufiya]

Hello @Patrick, yes future data up to the current date of yesterday does exist in teradata. 

[Reeza]

%macro loop_demo(n=);

%do i=0 %to %eval(&n-1);

    data _null_;
        %*increment date;
        date = intnx('month', &cmbtd, &i, 'b');;
        call symputx('yearWant', year(date));
        call symputx('monthWant', month(date));
    run;

%put Year = &yearWant;
%put Month = &monthWant;

%end;
%mend;

%loop_demo(n=4);