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
Hello @LuisDalmasso,
I think the reason for the error message is the missing semicolon after the first QUIT statement.
Can you turn on options MPRINT, then run the macro and post the full log?
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.
Hello @LuisDalmasso,
I think the reason for the error message is the missing semicolon after the first QUIT statement.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.