topic Re: macro sql loop - from current month generate the next 3 months in SAS Programming

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

sufiya — Fri, 16 Feb 2018 18:04:33 GMT

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;

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

Reeza — Fri, 16 Feb 2018 20:26:32 GMT

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

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

sufiya — Fri, 16 Feb 2018 23:16:38 GMT

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!

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

Patrick — Fri, 16 Feb 2018 23:31:16 GMT

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

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

Reeza — Fri, 16 Feb 2018 23:37:39 GMT

%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);

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

sufiya — Sun, 18 Feb 2018 15:18:11 GMT

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