BookmarkSubscribeRSS Feed
sufiya
Quartz | Level 8

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; 

 

8 REPLIES 8
Astounding
PROC Star

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; 
sufiya
Quartz | Level 8

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

 

sufiya
Quartz | Level 8

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


 

 

Satish_Parida
Lapis Lazuli | Level 10

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;
sufiya
Quartz | Level 8

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.

Satish_Parida
Lapis Lazuli | Level 10
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
sufiya
Quartz | Level 8

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;

 

Reeza
Super User

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 

 


 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1419 views
  • 0 likes
  • 4 in conversation