DATA Step, Macro, Functions and more

macro loop to get next 3 months

Reply
Contributor
Posts: 34

macro loop to get next 3 months

[ Edited ]

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; 

 

Super User
Posts: 6,543

Re: macro loop to get next 4 months

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; 
Contributor
Posts: 34

Re: macro loop to get next 4 months

Posted in reply to Astounding

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

 

Contributor
Posts: 34

Re: macro loop to get next 3 months

Posted in reply to Astounding

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'"));


 

 

Frequent Contributor
Posts: 109

Re: macro loop to get next 4 months

[ Edited ]

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;
Contributor
Posts: 34

Re: macro loop to get next 4 months

Posted in reply to Satish_Parida

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.

Frequent Contributor
Posts: 109

Re: macro loop to get next 4 months

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
Contributor
Posts: 34

Re: macro loop to get next 4 months

Posted in reply to Satish_Parida

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;

 

Super User
Posts: 22,874

Re: macro loop to get next 4 months

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 

 


 

Ask a Question
Discussion stats
  • 8 replies
  • 175 views
  • 0 likes
  • 4 in conversation