BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Cello23
Quartz | Level 8

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

 

View solution in original post

11 REPLIES 11
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Cello23
Quartz | Level 8
%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

Kurt_Bremser
Super User

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;
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

 

Cello23
Quartz | Level 8

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

 

Astounding
PROC Star

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.

Quentin
Super User

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

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
Cello23
Quartz | Level 8
thanks
Ksharp
Super User

%LET YEAR = 2016;
%LET MONTH = 12;
 

%LET MONTH_NEXT = %eval(%sysfunc(mod(&MONTH,12))+1); 

%put NOTE: &MONTH_NEXT ;
Cello23
Quartz | Level 8
thanks!!!!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to choose a machine learning algorithm

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.

Discussion stats
  • 11 replies
  • 1754 views
  • 10 likes
  • 6 in conversation