SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Proc append with DBCREATE_TABLE_OPTS option

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 17
Accepted Solution

Proc append with DBCREATE_TABLE_OPTS option

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


Accepted Solutions
Solution
‎04-25-2017 11:33 AM
SAS Employee
Posts: 203

Re: Proc append with DBCREATE_TABLE_OPTS option

[ Edited ]

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.

View solution in original post


All Replies
Solution
‎04-25-2017 11:33 AM
SAS Employee
Posts: 203

Re: Proc append with DBCREATE_TABLE_OPTS option

[ Edited ]

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.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 1 reply
  • 219 views
  • 0 likes
  • 2 in conversation