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