topic Re: Question about %Let in SAS Data Science

Question about %Let

Cello23 — Thu, 14 Apr 2016 09:37:24 GMT

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

Re: Question about %Let

RW9 — Thu, 14 Apr 2016 09:46:55 GMT

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.

Re: Question about %Let

Cello23 — Thu, 14 Apr 2016 10:05:14 GMT

%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

Re: Question about %Let

Kurt_Bremser — Thu, 14 Apr 2016 10:13:16 GMT

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;

Re: Question about %Let

RW9 — Thu, 14 Apr 2016 10:24:35 GMT

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.

Re: Question about %Let

Astounding — Thu, 14 Apr 2016 11:41:24 GMT

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.

Re: Question about %Let

Cello23 — Thu, 14 Apr 2016 12:53:11 GMT

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

Re: Question about %Let

RW9 — Thu, 14 Apr 2016 13:14:40 GMT

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;

Re: Question about %Let

Quentin — Thu, 14 Apr 2016 23:27:39 GMT

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

Re: Question about %Let

Ksharp — Fri, 15 Apr 2016 07:53:45 GMT


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

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

%put NOTE: &MONTH_NEXT ;

Re: Question about %Let

Cello23 — Fri, 22 Apr 2016 09:09:36 GMT

thanks!!!!

Re: Question about %Let

Cello23 — Fri, 22 Apr 2016 09:09:54 GMT

thanks