topic Re: Proc append with DBCREATE_TABLE_OPTS option in SAS Data Management

Proc append with DBCREATE_TABLE_OPTS option

AravindPalanichamy — Sat, 25 Mar 2017 20:28:57 GMT

Hi,

Could any one confirm whether we can use proc append statement to load data in to Teradata. Is this the right way to do, can we use dbcreate_table_opts option in proc append or it is only dataset option. One of user getting the following two errors when using proc append with db clause. Is this the right way?

33769 /*---- Loading with Multiload ----*/
33770 %put %str(NOTE: Appending data ...);
33771
33772 proc append base = dev.ipact_job_journal(DBCREATE_TABLE_OPTS = 'primary index NONAME1
33772 ! ("job_name","output_table","step_num","step_start_date","step_start_time","user_name") '
21828 The SAS System 13:29 Thursday, March 16, 2017

33773 MULTILOAD = YES TPT = NO )
33774 data = &etls_lastTable (&etls_tableOptions) force;
33775 run;
33776
33777 %rcSet(&syserr);
33778
33779 %mend etls_loader;
33780 %etls_loader;
NOTE: Appending data ...
MPRINT(ETLS_LOADER): proc append base = dev.ipact_job_journal(DBCREATE_TABLE_OPTS = 'primary index NONAME1
("job_name","output_table","step_num","step_start_date","step_start_time","user_name") ' MULTILOAD = YES TPT = NO ) data =
WORK.W3H7X1P () force;
MPRINT(ETLS_LOADER): run;

NOTE: Appending WORK.W3H7X1P to DEV.ipact_job_journal.
ERROR: Shell escape is not valid in this SAS session.
NOTE: There were 1 observations read from the data set WORK.W3H7X1P.
NOTE: 0 observations added.
NOTE: The data set DEV.ipact_job_journal has . observations and 7 variables.
ERROR: Native Multiload could not be invoked. Verify that Multiload can be invoked outside of SAS.
NOTE: Statements not processed because of errors noted above.
NOTE: PROCEDURE APPEND used (Total process time):
real time 0.24 seconds
cpu time 0.02 seconds

Re: Proc append with DBCREATE_TABLE_OPTS option

JBailey — Mon, 27 Mar 2017 15:10:07 GMT

Hi @AravindPalanichamy

You can use PROC APPEND to load data into Teradata. I have written a paper which will help explain all this.

Effectively Moving SAS Data into Teradata - Jeff Bailey

This paper may help, too.

Teradata Parallel Transporter: Loading Your SAS® Data Just Became Easier - Jeff Bailey

The gist of this is that non-TPT multi-load actually invokes the Teradata multiload utility via a shell command. This is why you are seeing this error message:

ERROR: Shell escape is not valid in this SAS session.

And this one...

ERROR: Native Multiload could not be invoked. Verify that Multiload can be invoked outside of SAS.

These errors mean that SAS is not allowed, or not allowing your session, to kick off this utility. Your SAS admin may be able to help you with this. If they won't allow the shell command to run, then TPT Multiload could be used. It doesn't require running the Multiload utility.

Re: Proc append with DBCREATE_TABLE_OPTS option

Kris_Mr_lee — Fri, 26 Jan 2018 19:27:27 GMT

Thank you @JBailey for your response on this.

We have been noticing these errors have been showing up on our deployed jobs recently.

ERROR: Shell escape is not valid in this SAS session.

ERROR: Native Multiload could not be invoked. Verify that Multiload can be invoked outside of SAS.

Wondering if you are aware of the steps to troubleshoot and correct this issue.

Any information on these errors would be appreciated.

Thanks & Regards,
Kris.