topic Re: WARNING when using EXECUTE() to run Redshift COPY command in SAS Data Management

WARNING when using EXECUTE() to run Redshift COPY command

Tom — Tue, 08 May 2018 20:35:04 GMT

Does anyone know how to stop PROC SQL from writing WARNING: line into SAS log when remote database sends back INFO message when using EXECUTE() statement in PROC SQL to push commands into the database?

I was using SAS/Access to ODBC with a Redshift database and issuing COPY command to load into a table from files on S3 bucket. The command runs fine, but I get a WARNING in the SAS log.

WARNING: During execute: INFO:  Load into table 'bucket_test' completed, 19 record(s) loaded
         successfully.

Is there some SAS option, PROC SQL option or ODBC connection option that will make PROC SQL not describe that note as a WARNING? Or perhaps some Redshift option that could stop it from sending the status message back?

proc sql noprint ;
  connect to odbc .....;
  execute(
    copy &schema..&table
    from %squote(s3://&bucket/sas_upload&path/&fname)
    access_key_id &keyid  secret_access_key &secret
    delimiter '|'  gzip  csv
    blanksasnull  emptyasnull
    dateformat 'auto'  timeformat 'auto'
  ) by odbc;
quit;

Re: WARNING when using EXECUTE() to run Redshift COPY command

ChrisNZ — Tue, 08 May 2018 23:10:41 GMT

Maybe

proc sql nowarn;

would work?

Re: WARNING when using EXECUTE() to run Redshift COPY command

Tom — Wed, 09 May 2018 01:42:08 GMT

@ChrisNZ wrote:

Maybe

proc sql nowarn;

would work?

Good idea, but unfortunately it does not help.

Re: WARNING when using EXECUTE() to run Redshift COPY command

ChrisNZ — Wed, 09 May 2018 02:50:57 GMT

Maybe a setting in the ODBC driver to not pass back messages then?

But the message is useful, not a warning, and SAS misinterprets it.

So it looks like it's time to raise a defect with tech support.

As a workaround for clean log, a quick proc printto may be necessary until the defect is fixed.

You can even read the log file right away and output the useful information back to the log.