Convert normal sql with loop into SQL Pass Thru with loop

[imdickson]

Hi All. I have a PROC SQL script that is written with a loop in WHERE statement.

%let id='2019-JAN-E6-S'; (Can have more than 1 ID in actual DB)
proc sql;
select distinct INCIDENT_RK into:incidentrk1-:incidentrk&SysMaxLong from ecmdb.INCIDENT_LIVE where INCIDENT_ID = &id;

select count (distinct incident_rk) into:cnt from ecmdb.INCIDENT_LIVE where INCIDENT_ID = &id;
quit;



delete from ecmdb.INCIDENT_UDF_CHAR_VALUE where INCIDENT_RK in (&incidentrk1

%do i=2 %to &cnt;

,&&incidentrk&i

%end;

)

Due to performance issue, I would like to convert my above script into pass-thru but it wont work. What is wrong with my code?

proc sql noprint;
connect to ODBC(datasrc=DEVECM user=ecm password=1 insertbuff = 10000);
EXECUTE (
delete from ecm.incident_udf_num_value where incident_rk IN

(&incidentrk1

%do i=2 %to &cnt;

,&&incidentrk&i

%end;

)


) BY ODBC ;
;
disconnect from ODBC;
quit;

I understand that pass-thru statememt have to follow Microsoft SQL standard but I am not familiar with QUOTE etc statement that will make my loop works. Can anyone further assist me?

[PaigeMiller]

First, you need to get your SQL passthru to work properly for one or two variables WITHOUT macros and WITHOUT macro variables. Once you have that, you should be able to turn it into something that works using macro language. If you don't have working code without macros and without macro variables, then you will never get it to work with macros.

So please show us working SAS code without macros and without macro variables.

[imdickson]

Thanks buddy. I will show later as i just got off from my work. Thank you so much.

[Tom]

Why do you have the %DO loop?

Are there too many values to put into a single macro variable?

let id='2019-JAN-E6-S'; 
proc sql;
select distinct INCIDENT_RK
 into :incidentrk_list separated by ',' 
from ecmdb.INCIDENT_LIVE 
where INCIDENT_ID in (&id)
;
%if &sqlobs %then %do;
delete from ecmdb.INCIDENT_UDF_CHAR_VALUE 
  where INCIDENT_RK in (&incidentrk_list)
;
%end;
quit;

[imdickson]

Ok thanks for suggesting separated by comma option. I believe by using this, I do not have to loop at all. I am currently out of office but will that work with Pass-thru statement without any %DO loop?

[Tom]

As long as the syntax is valid in the remote database.

Note that the macro variable needs to be built in the SAS (local) environment.  

It might be better to push the whole logic into the remote database, perhaps by making a temporary table instead of a list of values in one or more SAS macro variables.

[Patrick]

There was a semicolon after the %do statement. Below code should create valid SQL syntax.

options mprint;
%let cnt=3;
%let incidentrk1=1;
%let incidentrk2=2;
%let incidentrk3=3;

%macro test();
  proc sql noprint noexec;
    connect to ODBC(datasrc=DEVECM user=ecm password=1 insertbuff = 10000);
    EXECUTE (
      delete from ecm.incident_udf_num_value where incident_rk IN
        (&incidentrk1
      %do i=2 %to &cnt;
          ,&&incidentrk&i
      %end;
    ) ) BY ODBC;
    ;
    disconnect from ODBC;
  quit;
%mend;

Having said that: I believe there is nothing in your SQL that SAS couldn't push to the database if formulated as SAS SQL. Eventually consider code as below (which of course you can also write as explicit pass-through SQL).

proc sql;
  delete from ecmdb.incident_udf_num_value 
  where incident_rk in 
    (select distinct INCIDENT_RK from ecmdb.INCIDENT_LIVE where INCIDENT_ID = &id)
  ;
quit;