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
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);
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
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);
Thank's !!!
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;
The SAS Users Group for Administrators (SUGA) is open to all SAS administrators and architects who install, update, manage or maintain a SAS deployment.
Learn how to install the SAS Viya CLI and a few commands you may find useful in this video by SAS’ Darrell Barton.
Find more tutorials on the SAS Users YouTube channel.