topic Re: macro loop to get next 4 months in SAS Programming

macro loop to get next 3 months

sufiya — Thu, 15 Feb 2018 10:48:08 GMT

Hi,

A bit lost here, I am using the logic on how to get previous months - reversing to get next month but it doesnt seem to be working ...if someone can please help....

this is to get previous 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-08-01 00:00:00';
%let ENDRep2='2017-08-31 00:00:00';


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

/****Loop: ***i=1=current month , i=2 Prevous Month, i=3 Prevous previous Month, i=4 Previous previous previous 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;

and using that to get the next 3 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-31 00:00:00';


%let startRep2='2017-10-01 00:00:00';
%let ENDRep2='2017-10-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;

Re: macro loop to get next 4 months

Astounding — Thu, 15 Feb 2018 07:48:34 GMT

In the ballpark, but needs to be tested:

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

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

Re: macro loop to get next 4 months

Satish_Parida — Thu, 15 Feb 2018 10:24:47 GMT

You can use intnx function called inside sysfunc for better sollution

Here is my approach. Let us know it it helped.

%let startRep=01Nov2017; /*Used Standard SAS Date formats*/

%macro sqlloop;
  %do i=0 %to 3;
    %let j=%eval(&i+1);
    %let startRep&j=%sysfunc(intnx(month,"&startRep."d,-&i.));
    %put &&startRep&j;
  %end;
%mend sqlloop;
%sqlloop;

Re: macro loop to get next 4 months

sufiya — Thu, 15 Feb 2018 14:14:04 GMT

THank you @Astounding, I tried your logic and had to changed %if &repMthCM+&i>12 to %if &repMthCM+&i>1, as i want to get the next the next 3 months after current month September then want October, November and December months to show....(sorry for the confusion did not mean the next 4 months).

the code below is generating past months i.e current month September - past months August, July and June showing.

/****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=&repMthCM+&i-12 ;
                   %let repYr=&repYrCM+1;
            %end;

            %else %do;
                    %let repMth=&repMthCM+&i ;
                    %let repYr=&repYrCM;
            %end;   
create table test1 as 
select
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,
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=9 then put(a.cycle_run_year+1, 4.) else put(a.cycle_run_year, 4.) end )
 ||'-'||(case when a.cycle_run_month=9 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 
 from CalendarSource

Re: macro loop to get next 4 months

sufiya — Thu, 15 Feb 2018 14:50:18 GMT

Thank you @Satish_Parida ! when I tried your logic received an error -> ERROR: There is no matching %DO statement for the %END. This statement will be ignored.

Re: macro loop to get next 4 months

Satish_Parida — Thu, 15 Feb 2018 14:53:38 GMT

Runs fine for me.
Log for reference.

4 %let startRep=01Nov2017; /*Used Standard SAS Date formats*/
5
6 %macro sqlloop;
7 %do i=0 %to 3;
8 %let j=%eval(&i+1);
9 %let startRep&j=%sysfunc(intnx(month,"&startRep."d,-&i.));
10 %put &&startRep&j;
11 %end;
12 %mend sqlloop;
13 %sqlloop;
21124
21093
21063
21032

Re: macro loop to get next 4 months

sufiya — Thu, 15 Feb 2018 15:21:33 GMT

if I run your query by it self - then yes no errors.

but when I add the rest of the code as

%let startRep=01Nov2017; /*Used Standard SAS Date formats*/

%macro sqlloop;
  %do i=0 %to 3;
    %let j=%eval(&i+1);
    %let startRep&j=%sysfunc(intnx(month,"&startRep."d,-&i.));
    %put &&startRep&j;
  %end;

PROC SQL; 
CREATE TABLE TEST AS 

SELECT 
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, 
Cycle_Start_Date as Cycle_Start_Day, 
Animal, 
put(cycle_run_year, 4.)||'-'||(case when a.cycle_run_month>10 then '0'||put(a.cycle_run_month, 1.0) 
else put(cycle_run_month, 2.0) end ) as cycle_run_YR_MTH, 
cycle_run_month, 
cycle_run_year, 
datepart(cycle_start_date) as Cycle_Start_DateO format = date9., 
DATEPART(Cycle_End_Date) AS cycle_END_dateO FORMAT = DATE9., 
(case when cycle_run_month=9 then put(cycle_run_year+1, 4.) 
else put(cycle_run_year, 4.) end ) ||'-'||(case when a.cycle_run_month=9 then '01' 
when cycle_run_month>12 then '0'||put(cycle_run_month+1, 1.0) 
else put(cycle_run_month+1, 2.0) end ) as repMTH /*** put(cycle_run_year, 4.)||'-'||
(case when cycle_run_month>9 then '0'||put(cycle_run_month+1, 1.0) else put(cycle_run_month+1, 2.0) 
end ) as repMTH ***/ 

from calendar.source 
where cycle_run_month =&repMth /
***The Marco process has make the next month for calendar cycle****/ and cycle_run_year =&repYr 
%END; 
QUIT; 
%mend; 
%sqlloop;

Re: macro loop to get next 4 months

Reeza — Thu, 15 Feb 2018 16:09:16 GMT

Consider where the %END of the loop is.

@sufiya wrote:

if I run your query by it self - then yes no errors.

but when I add the rest of the code as

Re: macro loop to get next 3 months

sufiya — Thu, 15 Feb 2018 17:35:48 GMT

would anyone please be able to help and tell what needs to be done? to get the next 3 months October,November, December - if current month is September? for whatever reason i either get August,July,June or January, February data

/*** 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-10-01 00:00:00';
%let ENDRep1='2017-10-31 00:00:00';


%let startRep2='2017-10-01 00:00:00';
%let ENDRep2='2017-10-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=&repMthCM+&i-12 ;
                   %let repYr=&repYrCM+1;
            %end;

            %else %do;
                    %let repMth=&repMthCM+&i ;
                    %let repYr=&repYrCM;
            %end;  
 
PROC SQL;
CREATE TABLE Calendar AS
SELECT 
TRANSACTION_TYPE,
put(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,
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=9 then put(a.cycle_run_year+1, 4.) else put(a.cycle_run_year, 4.) end )
    ||'-'||(case when a.cycle_run_month=9 then '01'
                 when a.cycle_run_month>12 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 ***/

select 
TRANSACTION_TYPE,
CYCLE_CODE,
Cycle_Start_Date,
 
JOURNALIZATION_DATE,
cycle_run_month,
cycle_run_year,
cycle_start_date,
Cycle_End_Date


from  VIEWA
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.gl 
left join lm_gl2 e2 on a. TRANSACTION_TYPE = e2.ftr 

%END; 
QUIT;
%mend; 
%sqlloop;


%macro sqlloop; 
 PROC SQL; 
 %DO i=1 %TO 4; 
 %let repMthStart=%sysfunc(dequote("'%sysfunc(intnx(month,&cmbtd, +&i+1, b),yymmdd10.) 00:00:00'"));
 %let repMthEnd=%sysfunc(dequote("'%sysfunc(intnx(month,&cmetd, +&i+1, e),yymmdd10.) 00:00:00'"));