topic Re: Creating Tables in Database through Sas in SAS Programming

Creating Tables in Database through Sas

deleted_user — Sat, 05 Apr 2008 14:39:11 GMT

I usually use Toad or SqlPlus to create a table in Datawarehouse. Can we use some command in SAS to create tables in warehouses. I know Proq Sql can be sued to retreive data from a warehouse, not sure if there is a command to create table in warehouse?

Thanks in Advance

Re: Creating Tables in Database through Sas

deleted_user — Sat, 05 Apr 2008 20:29:31 GMT

Depending on your needs, here are 2 ways to create your Oracle table from SAS. Find documentation at http://support.sas.com/onlinedoc/913/docMainpage.jsp.

1. Bulk Load to invoke Oracle SQL*Loader for lots of rows
LIBNAME sasflt 'SAS-Data-Library';
LIBNAME ora_air ORACLE
USER=testuser
PASS=testpass
PATH='ora8_flt'
SCHEMA=statsdiv;

PROC SQL STIMER _METHOD THREADS;
CREATE TABLE ora_air.flights98 (BULKLOAD=YES
BL_OPTIONS='ERRORS=899,LOAD=5000'
) AS
SELECT *
FROM sasflt.flt98
;
QUIT;

2. PROC DBLOAD
LIBNAME adlib 'SAS-data-library';
LIBNAME dlib 'SAS-data-library';

PROC DBLOAD DBMS=ORACLE DATA=dlib.rateofex;
USER=testuser;
ORAPW=testpass;
PATH='myorapath';
TABLE=exchange;
ACCDESC=adlib.exchange;
RENAME fgnindol=fgnindolar 4=dolrsinfgn;
NULLS updated=n fgnindol=n 4=n country=n;
LOAD;
RUN;

PROC DBLOAD DBMS=ORACLE;
USER=testuser;
ORAPW=testpass;
PATH='myorapath';
SQL GRANT SELECT ON testuser.exchange TO pham;
RUN;

Re: Creating Tables in Database through Sas

deleted_user — Sun, 06 Apr 2008 15:51:18 GMT

Hi,

Thanks for replying!

I figured out another way of doing this, if you use the below command, you can pretty much execute the DBMS cOMMANDS IN SAS.

proq sql;
execute;

after connecting to the dbms through remote server, we can just wqrite execute and you can write statements to create table directly in the database, which it can execute.