SAS PIPE not working after a failed PROC SQL

Question

I use below lines to execute a perlcommand in a macro

 %let perlcommand="/home/diham/test.pl read";

 filename myfh pipe "&perlcommand";

During the first iteration it executes correctly and myfh is assigned. My PROC SQL after that fails, as the tabe is already existing and this is expected.

create table dw.test_table(BULKLOAD=YES BL_DELETE_DATAFILE=YES 
                           BL_OPTIONS='silent=(header,feedback),errors=0'
           BL_SQLLDR_PATH="/opt/app/oracle/product/10.2.0.2/client/bin/sqlldr " )
as select * from dataset;
ERROR: The ORACLE table TEST_TABLE has been opened for OUTPUT. This table already exists, or there is a name conflict with an
   existing object. This table will not be replaced. This engine does not support the REPLACE option.

Next step in my code is to again call the above macro and run a select query. But this time the command is not getting executed.

%macro getCredentials (readwrite, database, acct );

/* Call the perl script and get the credentials */

%let credential = /home/diham/test.pl &readwrite &database  &acct;

/*reading the material-set name and getting the credentials (username and password)*/
data _null_ ;
put "Executing  getCredentials";
run;

LIBNAME DW CLEAR;
filename myfh clear;

filename myfh pipe "&credential";
data CREDS;
length username $ 20 password $ 20;
infile myfh delimiter=',' truncover;
input username $ password $;
run;

filename myfh clear;

data CREDS;
set CREDS;
call symput("username",username);
call symput("password",password);
run;

Can someone help me here. Thanks!

BellevueBob BellevueBob · Accepted Answer · 2013-07-06T17:56:02

A few suggestions:

After executing your pipe command, clear the fileref by adding this statement:

filename myfh clear;

If you want to load a new table every time, you should consider executing a PROC DELETE step immediately before creating the table (before PROC SQL):

proc delete data=dw.test_table;
run;

The advantage is that `proc delete' will drop the table if if exists and only show a warning note if the table does NOT exist (i.e. no error).

Finally, if you want to programmatically control your PROC SQL session, you can use the NOERRORSTOP option. That will prevent PROC SQL from going into syntax-check mode after an error. Your code can examine the &SQLXRC automatic macro variable after each step and deal with things are required.

If these hints do not help, please revise your question with an actual, complete SAS macro that you are trying to run. No need to show all your existing code, just show a subset that illustrates your exact problem.

SAS PIPE not working after a failed PROC SQL

1 Answers