NEED HELP asap very new to SAS - trying to get the next 3 months to display from current month.
i.e. if current month is September then the next 3 months will be October, November and December but all I was able to find online was how to generate the past 3 months - how can I using the same logic get future 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-12-01 00:00:00';
%let ENDRep2='2017-12-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;
PROC SQL;
CONNECT TO TERADATA(USER="USERI" PASSWORD="PASSWORD" SERVER="X" mode=teradata
DATABASE="X");
CREATE TABLE B_CalendarCycleData&i AS
SELECT
a.TRANSACTION_TYPE,
a.FEATURE_CODE,
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,
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,
e1.gl_DESC1,
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=12 then put(a.cycle_run_year+1, 4.) else put(a.cycle_run_year, 4.) end )
||'-'||(case when a.cycle_run_month=12 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
/***
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 ***/
FROM CONNECTION TO TERADATA(
select
TRANSACTION_TYPE,
FEATURE_CODE,
CYCLE_CODE,
Cycle_Start_Date,
TELCO,
JOURNALIZATION_DATE,
cycle_run_month,
cycle_run_year,
cycle_start_date,
Cycle_End_Date
from GRP_ENH_VW
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.TRANSACTION_TYPE
DISCONNECT FROM TERADATA;
%END;
QUIT;
%mend;
%sqlloop;
%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 your code, what do you think happens when the month is 12? It switches to 13 which isn't valid I suspect.
Did you look at the SAS macro appendix to loop over dates? It's exactly what you want.
You need to use INTNX to increment your dates otherwise you have to deal with issues like crossing the year boundaries.
And do you have to loop this?
Hi @Reeza, When I do that the query returns with no errors but 0 rows/results.
Yes - want to use the same logic and reverse it to get the next following 3 months.
thank you for your help!
@sufiya Well, do these future months exist in your Teradata source table?
Hello @Patrick, yes future data up to the current date of yesterday does exist in teradata.
%macro loop_demo(n=);
%do i=0 %to %eval(&n-1);
data _null_;
%*increment date;
date = intnx('month', &cmbtd, &i, 'b');;
call symputx('yearWant', year(date));
call symputx('monthWant', month(date));
run;
%put Year = &yearWant;
%put Month = &monthWant;
%end;
%mend;
%loop_demo(n=4);
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: