DATA Step, Macro, Functions and more

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

Reply
Contributor
Posts: 34

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

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;

 

Super User
Posts: 23,357

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

            %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? 

 

 

Contributor
Posts: 34

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

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!  

Respected Advisor
Posts: 4,698

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

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

Contributor
Posts: 34

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

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

Super User
Posts: 23,357

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

%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);
Ask a Question
Discussion stats
  • 5 replies
  • 211 views
  • 0 likes
  • 3 in conversation