topic Re: SAS Invalid Option in SAS Programming

SAS Invalid Option

iSAS — Mon, 16 Nov 2020 13:52:19 GMT

When i'm running the below query:

data alldata (dbcreate_table_opts='primary index(Detection_Strat_ID,End_Date)');
set table1 table2;
run;

I'm having this error:

26 data alldata (dbcreate_table_opts='primary index(Detection_Strat_ID,End_Date)');
___________________
22
ERROR 22-7: Invalid option name DBCREATE_TABLE_OPTS.

May I know how to fix this? Thanks

Re: SAS Invalid Option

PaigeMiller — Mon, 16 Nov 2020 13:58:10 GMT

This option cannot be used with an ordinary SAS DATA step.

It can be used with the DS2 language within SAS.

Re: SAS Invalid Option

Kurt_Bremser — Mon, 16 Nov 2020 13:57:11 GMT

The dataset option dbcreate_table_opts is valid when SAS/ACCESS for Relational Databases is used, it is not valid for library WORK.

Re: SAS Invalid Option

iSAS — Mon, 16 Nov 2020 14:02:24 GMT

Hello KurtBremser. Thank you for responding. I tried this code:

proc append
base=td.vcm (fastload=yes sessions=4)
data=alldata
force;
run;

BUt i'm receiving this error:

ERROR: Teradata connection: No more room in database BMGU_ROSA_TST. Correct error and restart as an APPEND process with option
TPT_RESTART=YES. Since no checkpoints were taken, if the previous run used FIRSTOBS=n, use the same value in the restart.

OUr teradata database has enough space though. May I know how to fix this?

Re: SAS Invalid Option

Kurt_Bremser — Mon, 16 Nov 2020 14:32:53 GMT

A search for that message led me to this accepted solution here on the communities: https://communities.sas.com/t5/SAS-Enterprise-Guide/Error-Teradata-insert-No-more-room-in-database/m-p/442747/highlight/true#M28565

See if it is helpful.

Re: SAS Invalid Option

Tom — Mon, 16 Nov 2020 14:42:26 GMT

Combine your two sets of code.

Add the options you tried to apply to a SAS dataset to the dataset you are trying to create in your Teradata database.

If you don't tell Teradata the primary index then it will pick the first variable in the dataset. If that variable does not have evenly distributed values then you can overload one data/compute node even when the database as a whole has plenty of space.