Hi,
I have this var:
%LET YEAR = 2016;
%LET MONTH = 2;
I have to get a var of the next month:
%LET MONTH_NEXT = &MONTH+1;
(MONTH_NEXT) PUT -> 2 + 1 (wrong – I need the result is 3)
what is my error ?
Thanks
Well typo on my part there - note I can't test this:
data _null_; call symput('molog',cats("MOLOGATM",put(&anno.,z4.),put(&messe.,z2.))); new_date=intnx('month',mdy(&anno.,&messe.,1),1) call symput('molog_suc',cats("MOLOGATM",put(year(new_date),z4.),put(month(new_date),z2.))); run;
Why are you processing "data" in macro? Macro is a tool for generating text, it is not a "data" manipulation language, that is what Base SAS is for.
data temp; year=2016; month=2; day=1; result=month(intnx('month',mdy(month,day,year),1)); put _all_; run;
You will note the use of Base SAS functions to move dates by intervals.
%LET ANNO = 2016;
%LET MESE = 2;
%LET DT_INIZIO = '28MAR2016'D;
%LET DT_FINE = '01APR2016'D;
%LET MESE_SUC = &MESE+1;
%LET MOLOG = MOLOGATM&ANNO&MESE;
% LET MOLOG_SUC = MOLOGATM&ANNO&MESE_SUC;
%syslput ANNO=&ANNO;
%syslput MESE=&MESE;
%syslput MOLOG = &MOLOG;
%syslput MOLOG_SUC = &MOLOG_SUC;
%syslput DT_INIZIO = &DT_INIZIO;
%syslput DT_FINE = &DT_FINE;
rsubmit UNIX_601;
libname UNI clear;
libname UNI DB2 user=&user_ora PW=&pw_ora db=&db schema=UNI defer=yes CONNECTION=GLOBALREAD UTILCONN_TRANSIENT=YES;;
PROC SQL;
CREATE TABLE T01 AS
SELECT B.CO_ATM, B.CODCAB, B.DB_OPER, B.CO_CAU_OPER_ATM, B.CO_SUB_CAU_OPE_ATM, B.FL_CARTA_BANCA,
SUM(B.MAX_PREL) AS MAX_PREL, SUM( B.IM_OPER_ATM) AS IMP, COUNT (*) AS N
FROM (
SELECT A.CO_ATM, A.CODCAB, A.DB_OPER, A.CO_CAU_OPER_ATM, A.CO_SUB_CAU_OPE_ATM, A.FL_CARTA_BANCA,
(CASE WHEN IM_OPER_ATM >750 then 1 else 0 end )AS MAX_PREL, A.IM_OPER_ATM
FROM UNI.&MOLOG A
WHERE A.DT_OPE BETWEEN &DT_INIZIO AND &DT_FINE
/* AND CO_ATM = '00001'
AND CODCAB = '11717' */
UNION ALL
SELECT A.CO_ATM, A.CODCAB, A.DB_OPER, A.CO_CAU_OPER_ATM, A.CO_SUB_CAU_OPE_ATM, A.FL_CARTA_BANCA, /* PRENDO DENTRO ANCHE IL MESE SUCCESSIVO, IN QUESTO MODO SE HO UNA SETTIMANA CHE SCAVALLA RICAVA DA SUBITO IL DATO COMPLETO */
(CASE WHEN IM_OPER_ATM >750 then 1 else 0 end )AS MAX_PREL, A.IM_OPER_ATM
FROM UNI.&MOLOG_SUC A
WHERE A.DT_OPE BETWEEN &DT_INIZIO AND &DT_FINE
/* AND CO_ATM = '00001'
AND CODCAB = '11717' */
) AS B
GROUP BY B.CO_ATM, B.CODCAB, B.DB_OPER, B.CO_CAU_OPER_ATM, B.CO_SUB_CAU_OPE_ATM, B.FL_CARTA_BANCA
;
quit;
libname UNI clear;
endrsubmit;
This is the complete code.
Thanks
Replace
%LET MESE_SUC = &MESE+1;
with
data _null_;
suc_month = month(intnx('month',mdy(&mese,1,&anno),1));
call symput('mese_suc',put(suc_month,2.));
run;
Having applied some formatting, indentation, consitent casing, finishing macro variables, and correcting a few typos, I have the below, which I have added the data _null_ call:
%let anno=2016; %let mese=2; %let dt_inizio='28mar2016'd; %let dt_fine='01apr2016'd; data _null_; call symput('molog',cats("MOLOGATM",put(&anno.,z4.),put(&messe.,z2.))); call symput('molog_suc',cats("MOLOGATM",put(year(intnx('month',mdy(&anno.,&messe.,1),1)),z4.),put(month(z2.))); run; rsubmit unix_601; libname uni clear; libname uni db2 user=&user_ora. pw=&pw_ora. db=&db. schema=uni defer=yes connection=globalread utilconn_transient=yes; proc sql; create table T01 as select B.CO_ATM, B.CODCAB, B.DB_OPER, B.CO_CAU_OPER_ATM, B.CO_SUB_CAU_OPE_ATM, B.FL_CARTA_BANCA, sum(B.MAX_PREL) as MAX_PREL, sum(B.IM_OPER_ATM) as IMP, count(*) as N from ( select A.CO_ATM, A.CODCAB, A.DB_OPER, A.CO_CAU_OPER_ATM, A.CO_SUB_CAU_OPE_ATM, A.FL_CARTA_BANCA, case when IM_OPER_ATM >750 then 1 else 0 end as MAX_PREL, A.IM_OPER_ATM from UNI.&MOLOG. A where A.DT_OPE between &DT_INIZIO. and &DT_FINE. union all select A.CO_ATM, A.CODCAB, A.DB_OPER, A.CO_CAU_OPER_ATM, A.CO_SUB_CAU_OPE_ATM, A.FL_CARTA_BANCA, case when IM_OPER_ATM > 750 then 1 else 0 end as MAX_PREL, A.IM_OPER_ATM from UNI.&MOLOG_SUC. A where A.DT_OPE between &DT_INIZIO. and &DT_FINE. ) as B group by B.CO_ATM, B.CODCAB, B.DB_OPER, B.CO_CAU_OPER_ATM, B.CO_SUB_CAU_OPE_ATM, B.FL_CARTA_BANCA; quit; libname uni clear; endrsubmit;
I have no way of testing it however.
I have this error:
ERROR: File UNI.MOLOGATM20162.DATA does not exist.
ERROR: File UNI.A.DATA does not exist.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
690 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.02 seconds
cpu time 0.00 seconds
691
692 libname UNI clear;
NOTE: Libref UNI has been deassigned.
NOTE: Remote submit to UNIX_601 complete.
This code:
%LET ANNO = 2016;
%LET MESE = 2;
%LET DT_INIZIO = '28MAR2016'D;
%LET DT_FINE = '01APR2016'D;
data _null_;
call symput('molog',cats(put(&anno.,z4.),put(&mese.,z2.)));
call symput('molog_suc',cats(put(year(intnx('month',mdy(&anno.,&MESE.,1),1)),z4.),put(month(z2.)));
run;
rsubmit UNIX_601;
libname UNI clear;
libname UNI DB2 user=&user_ora PW=&pw_ora db=&db schema=UNI defer=yes CONNECTION=GLOBALREAD UTILCONN_TRANSIENT=YES;;
PROC SQL;
CREATE TABLE T01 AS
SELECT B.CO_ATM, B.CODCAB, B.DB_OPER, B.CO_CAU_OPER_ATM, B.CO_SUB_CAU_OPE_ATM, B.FL_CARTA_BANCA,
SUM(B.MAX_PREL) AS MAX_PREL, SUM( B.IM_OPER_ATM) AS IMP, COUNT (*) AS N
FROM (
SELECT A.CO_ATM, A.CODCAB, A.DB_OPER, A.CO_CAU_OPER_ATM, A.CO_SUB_CAU_OPE_ATM, A.FL_CARTA_BANCA,
(CASE WHEN IM_OPER_ATM >750 then 1 else 0 end )AS MAX_PREL, A.IM_OPER_ATM
FROM UNI.&MOLOG A
WHERE A.DT_OPE BETWEEN &DT_INIZIO AND &DT_FINE
/* AND CO_ATM = '00001'
AND CODCAB = '11717' */
UNION ALL
SELECT A.CO_ATM, A.CODCAB, A.DB_OPER, A.CO_CAU_OPER_ATM, A.CO_SUB_CAU_OPE_ATM, A.FL_CARTA_BANCA, /* PRENDO DENTRO ANCHE IL MESE SUCCESSIVO, IN QUESTO MODO SE HO UNA SETTIMANA CHE SCAVALLA RICAVA DA SUBITO IL DATO COMPLETO */
(CASE WHEN IM_OPER_ATM >750 then 1 else 0 end )AS MAX_PREL, A.IM_OPER_ATM
FROM UNI.&MOLOG_SUC A
WHERE A.DT_OPE BETWEEN &DT_INIZIO AND &DT_FINE
/* AND CO_ATM = '00001'
AND CODCAB = '11717' */
) AS B
GROUP BY B.CO_ATM, B.CODCAB, B.DB_OPER, B.CO_CAU_OPER_ATM, B.CO_SUB_CAU_OPE_ATM, B.FL_CARTA_BANCA
;
quit;
libname UNI clear;
endrsubmit;
thanks
Well typo on my part there - note I can't test this:
data _null_; call symput('molog',cats("MOLOGATM",put(&anno.,z4.),put(&messe.,z2.))); new_date=intnx('month',mdy(&anno.,&messe.,1),1) call symput('molog_suc',cats("MOLOGATM",put(year(new_date),z4.),put(month(new_date),z2.))); run;
Macro language does not perform math automatically. You have to tell it to perform the math by adding a function:
%let month_next = %eval(&month + 1);
That's not to contradict any of the other posts ... that's just to answer the question the way it was posed originally.
And in addition to %EVAL for doing integer math, %sysfunc() allows you to call many data step functions within the macro language, including INTNX and MDY for date calculations. So it's possible (and sometimes useful) to use the macro language for this sort of calculation.
Below code uses INTNX() to compute the SAS date of the next month, and formats that as yymmn6.
%LET ANNO = 2016; %LET MESE = 2; %let MOLOG_SUC = MOLOGATM%sysfunc(intnx(month ,%sysfunc(mdy(&mese,1,&anno)) ,1) ,yymmn6. ); %put &MOLOG_SUC;
That will return:
MOLOGATM201603
%LET YEAR = 2016;
%LET MONTH = 12;
%LET MONTH_NEXT = %eval(%sysfunc(mod(&MONTH,12))+1);
%put NOTE: &MONTH_NEXT ;
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.
Use this tutorial as a handy guide to weigh the pros and cons of these commonly used machine learning algorithms.
Find more tutorials on the SAS Users YouTube channel.