SAS -> Shell DB2 Passthrough and macro resolving

Question

I'm trying to automate a job that involves a lot of data being passed across the network and between the actual db2 server and our SAS server. What I'd like to do is take a traditional pass through...

proc sql;
 connect to db2(...);
 create table temp as 
 select * from connection to db2(
    select
     date 
    .......
    where 
     date between &start. and &stop.
); disconnect from db2;
quit;

into something like this:

x "db2 'insert into temp select date ...... where date between &start. and &stop.'";

I'm running into a few issues the first of which is db2 date format of 'ddMONyyyy'd which causes the shell command to terminate early. If I can get around that I think it should work.

I can pass a macro variable through to the AIX (SAS) server without the extra set of ' ' needed to execute the db2 command.

Any thoughts?

Answer 1

You might get around the single-quote around the date issue by setting off the WHERE clause with a parenthesis. I'm not sure that this will work, but it might be worth trying.

As far as X command, try something like the following:

%let start = '01jan2011'd;
%let stop  = '31dec2011'd;

%let command_text = db2 %nrbquote(')insert into temp select date ... where (date between &start. and &stop.)%nrbquote(');
%put command_text = &command_text;
x "&command_text";