topic Repeat query automatically in case of error in Administration and Deployment

Repeat query automatically in case of error

Cello23 — Mon, 17 Dec 2012 12:33:31 GMT

Hi guys,
i have this query:
rsubmit UNIX_601;
libname UC0 clear;
libname UC0 DB2 user=&user_ora PW=&pw_ora db=&db schema=UC0 defer=yes;
%MACRO MACROFILTRO (TABELLA=,USCITA=);
%local NOBS FILTRO;

    data _null_;
    set &TABELLA nobs=nobs;
    call symput('NOBS', nobs); stop;
    run;

%let NUM=1;
%Do %While (&NUM le &nobs);

proc sql noprint ;
   select "'"||CO_POPUP||"'" into :FILTRO separated by ','
       from &TABELLA (firstobs=&NUM obs=%EVAL(&NUM+0));
      quit;

      proc sql; options &LOG_LIST;
      connect to db2 (db=&db user=&user_ora PW=&pw_ora);
      create table OUT_TEMP1 as select *
      from connection to db2
      (

SELECT A.CO_AZIONE AS CO_POPUP , COUNT (A.NDG) AS TARGET
FROM UC0.MSLKTRGCONT A
where A.CO_AZIONE IN (&FILTRO)
AND a.CO_ESEC_CONT_MIS ='AA'
GROUP BY A.CO_AZIONE
order by 1

);
disconnect from db2;
quit;

proc append base = &USCITA data = OUT_TEMP1 FORCE; run;

%Let Num=%eval(&Num+20);
%End;

%MEND MACROFILTRO;
%MACROFILTRO(TABELLA=%Str(T01_ELENCO_POPUP_YEAR), USCITA=%Str(T18_TARGET_2 ));

libname UC0 clear;
endrsubmit;

I would like that if the table OUT_TEMP1 was not created because of an error:
ERROR: CLI cursor extended fetch error: [IBM][CLI Driver] SQL30081N A communication error has
been detected. Communication protocol being used: "TCP/IP". Communication API being

The query was repeated (with the last value & filter):
SELECT A.CO_AZIONE AS CO_POPUP , COUNT (A.NDG) AS TARGET
FROM UC0.MSLKTRGCONT A
where A.CO_AZIONE IN (&FILTRO)
AND a.CO_ESEC_CONT_MIS ='AA'
GROUP BY A.CO_AZIONE
order by 1

How can I do?
thank you very much

Re: Repeat query automatically in case of error

esjackso — Mon, 17 Dec 2012 13:05:02 GMT

You could try a macro check (%if ....%then...%do) using %sysfunc(open("OUT_TEMP1")) as the criteria. If it returns a 0 the dataset does not exist. I think this should work but I havent tried this in open macro code before so the syntax may be off a little.

Hope this helps

Re: Repeat query automatically in case of error

SASKiwi — Mon, 17 Dec 2012 19:28:47 GMT

Another option from the SAS documentation is the EXIST function (also using the open function is then redundant in your case):

%let dsname=sasuser.houses;

%macro opends(name);

%if %sysfunc(exist(&name)) %then

%let dsid=%sysfunc(open(&name,i));

%else %put Data set &name does not exist.;

%mend opends;

%opends(&dsname);

Re: Repeat query automatically in case of error

Cello23 — Tue, 26 Feb 2013 11:23:47 GMT

Thank's !!!

Re: Repeat query automatically in case of error

Cello23 — Wed, 27 Feb 2013 10:05:55 GMT

I would like that if the table out_temp1 did not exist the macro MACROFILTRO run 3 times after go out (I USED MACRO OPENDS)

I CAN NOT DO IT … PLEASE HELP :smileycry:

proc sql;

create table WORKUSI.ALL as

SELECT CO_POPUP

FROM db_neri.Ser_com_2012

where CO_POPUP IN ('FPTA0113','ABVI0113','BINP0213');

QUIT;

rsubmit UNIX_601 ;

proc datasets lib=work nolist; delete Out_temp1 T18c_5_46 ; quit;

%let Repeat_query=3;

%let count=0;

endrsubmit;

rsubmit UNIX_601;

libname UNI clear;

libname UNI DB2 user=&user_ora PW=&pw_ora db=&db schema=UNI defer=yes CONNECTION=GLOBAL UTILCONN_TRANSIENT=YES;

%MACRO MACROFILTRO (TABELLA=,USCITA=);

%local NOBS FILTRO;

data _null_;

set &TABELLA nobs=nobs;

call symput('NOBS', nobs); stop;

run;

%let NUM=1;

%Do %While (&NUM le &nobs);

proc sql noprint ; options &LOG_LIST;

select "'"||CO_POPUP||"'" into :FILTRO separated by ','

from &TABELLA (firstobs=&NUM obs=%EVAL(&NUM+0));

quit;

proc sql; options &LOG_LIST;

connect to db2 (db=&db user=&user_ora PW=&pw_ora);

create table OUT_TEMP1 as select *

from connection to db2

(

SELECT A.CO_AZIONE,count(*) as n

FROM UNI.MSANACONTST2013 A

WHERE A.CO_TIPOCONT = 'PA'

AND A.CO_AZIONE IN (&FILTRO)

group by A.CO_AZIONE

);

disconnect from db2;

quit;

%macro opends(name);

%let rc=%sysfunc(exist(&name));

%if &rc eq 1 %then %DO;

proc append base = &USCITA data = OUT_TEMP1 FORCE; run;

%Let Num=%eval(&Num+1);

%Let count=0;

%put Query ok;

%END;

%if rc eq 0 and &count<&Repeat_query %then %DO;

%put ERROR:DATA SET &USCITA , QUERY repeat n° &count;

%Let count=%eval(&count+1);

%END;

%IF rc eq 0 and &count>&Repeat_query %THEN %DO;

%put ERROR: DATA SET &USCITA QUERY LOOP EXIT;

%Let NUM = %eval(nobs+1);

%END;

%mend opends;

%opends(OUT_TEMP1);

%End;

%MEND MACROFILTRO;

%MACROFILTRO(TABELLA=%Str(ALL), USCITA=%Str(T18c_5_46 ));

libname UNI clear;

endrsubmit;