Macro Loop Problem

LuisDalmasso — Tue, 20 Feb 2024 12:14:07 GMT

Hello, when running this code I get an error and I can't detect the problem. It also stays running indefinitely.

This is the code


%global Error_Conexion_paso1;

/* Acceso a los datos para DMPRD - libreria DM SSGPMBI  */ 
LIBNAME DMPRD ORACLE  UPDATE_LOCK_TYPE=TABLE  OR_UPD_NOWHERE=NO  PATH="SASSTPRD.world"  SCHEMA=SAS_DM  AUTHDOMAIN="ORA_SAS_DM_Auth" ;
/* Acceso a los datos para CAS_DMPRD  */ 
LIBNAME CASDMPRD CAS  CASLIB=DMPRD  PORT=5570  SERVER="sasva-root.gcba.gob.ar" ;

%global sessref_option;
%let sessref_option=%sysfunc(getoption(SESSREF));
%let DAY = %sysfunc(today(),DDMMYYn8.);
%let HH = %sysfunc(hour(%sysfunc(datetime()) ) );
%let MM = %sysfunc(minute(%sysfunc(datetime()) ) );

%put &=DAY;
%put &=HH;
%put &=MM;

%global SessionName;
%LET SessionName = CAS_&DAY._&HH._&MM.;
%put &=SessionName;
%macro test;
proc sql ;

SELECT CAS_NAME, FECHA, (MONOTONIC()+500) as ID_CONTROL_CAS_CARGA length = 8 
  into :CASN1 - :CASN50
  ,:FE1 - :FE150
  ,:ID1 - :ID150
FROM  DMPRD.ETL_CONTROL_CAS_CARGA_RCE 
WHERE CAS_NAME NOT LIKE '%TABLON%'
AND FECHA = DHMS(TODAY(),0,0,0)
AND CAS_NAME <>  'RCE_SOLICITUD_EN_TRAMITE' 
AND TABLAS_CARGADAS = 1   
AND FECHA_FIN_CARGA_CAS is null 
;
QUIT
%let nobs=&sqlobs;
%do i=1 %to &nobs ;

%let Error_Conexion_paso1 = &syserrortext.;
%let FECHA_INICIO=%sysfunc(datetime());
%put &=(&&ID&i.);
%put &="&&CASN&i.";
%put &=&FECHA_INICIO.;

PROC SQL;
	UPDATE DMPRD.ETL_CONTROL_CAS_CARGA_RCE SET ID_CONTROL_CAS_CARGA=&&ID&i. where CAS_NAME ="&&CASN&i." and FECHA = DHMS(TODAY(),0,0,0) ;
quit;
%let FECHA_FIN=%sysfunc(datetime());
proc sql;
	UPDATE DMPRD.ETL_CONTROL_CAS_CARGA_RCE SET CARGA_CAS_EN_CURSO=0 where ID_CONTROL_CAS_CARGA=&&ID&i. and FECHA = DHMS(TODAY(),0,0,0) and &syserr. = 0;
quit;
%put &=syserr;
%end;
%mend;
%test;

The error is when you run the macro on the first UPDATE and then it stays running indefinitely

124 %mend;
125
126 %test;
NOTE: El operador "<>" se interpreta como "no igual".
&=(501)
&="VW_RCE_ADOPCIONES"
&=2024037910.85303
NOTE: The PROCEDURE SQL printed page 1.
NOTE: PROCEDURE SQL ha utilizado (Tiempo de proceso total):
real time 0.03 seconds
cpu time 0.03 seconds

NOTE: Line generated by the invoked macro "TEST".
126 UPDATE DMPRD.ETL_CONTROL_CAS_CARGA_RCE SET ID_CONTROL_CAS_CARGA=&&ID&i. where CAS_NAME ="&&CASN&i." and
______
180
126 ! FECHA = DHMS(TODAY(),0,0,0) ; quit;

ERROR 180-322: Statement is not valid or it is used out of proper order.

From now, thank you for you help

Re: Macro Loop Problem

Quentin — Tue, 20 Feb 2024 12:33:14 GMT

Can you turn on options MPRINT, then run the macro and post the full log?

Re: Macro Loop Problem

PaigeMiller — Tue, 20 Feb 2024 12:34:33 GMT

When you have a macro that produces an error in the log, please turn on the macro debugging option by running this line of code

options mprint;

Then run the macro again, and show us the log for the PROC SQL that has the error (not just the error messages, we need to see the code as it appears in the log as well, from PROC SQL down to the last note after the PROC). Please copy the log as text and paste it into the window that appears when you click on the </> icon. Please do this from now on whenever you have an error.

Re: Macro Loop Problem

FreelanceReinh — Tue, 20 Feb 2024 13:24:39 GMT

Hello @LuisDalmasso,

I think the reason for the error message is the missing semicolon after the first QUIT statement.

topic Re: Macro Loop Problem in SAS Programming

Macro Loop Problem

Re: Macro Loop Problem

Re: Macro Loop Problem

Re: Macro Loop Problem