Building models with SAS Enterprise Miner, SAS Factory Miner, SAS Visual Data Mining and Machine Learning or just with programming

Question about %Let

Accepted Solution Solved
Reply
Contributor
Posts: 51
Accepted Solution

Question about %Let

 

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


Accepted Solutions
Solution
‎04-14-2016 10:29 AM
Super User
Super User
Posts: 7,988

Re: Question about %Let

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


All Replies
Super User
Super User
Posts: 7,988

Re: Question about %Let

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.

Contributor
Posts: 51

Re: Question about %Let

%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

Super User
Posts: 7,854

Re: Question about %Let

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 7,988

Re: Question about %Let

[ Edited ]

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.

 

Contributor
Posts: 51

Re: Question about %Let

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

Solution
‎04-14-2016 10:29 AM
Super User
Super User
Posts: 7,988

Re: Question about %Let

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;

 

Super User
Posts: 5,516

Re: Question about %Let

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.

PROC Star
Posts: 1,324

Re: Question about %Let

Posted in reply to Astounding

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

Contributor
Posts: 51

Re: Question about %Let

thanks
Super User
Posts: 10,044

Re: Question about %Let


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

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

%put NOTE: &MONTH_NEXT ;
Contributor
Posts: 51

Re: Question about %Let

thanks!!!!
☑ This topic is solved.

Need further help from the community? Please ask a new question.

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