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;
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;
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
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'"));
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;
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.
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;
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.