Repeat query automatically in case of error

[Cello23]

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

[esjackso]

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

EJ

[SASKiwi · Accepted solution]

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);

[Cello23]

Thank's !!!

[Cello23]

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;