DATA Step, Macro, Functions and more

Creating Tables in Database through Sas

Reply
N/A
Posts: 0

Creating Tables in Database through Sas

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
N/A
Posts: 0

Re: Creating Tables in Database through Sas

Posted in reply to deleted_user
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;
N/A
Posts: 0

Re: Creating Tables in Database through Sas

Posted in reply to deleted_user
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.
Ask a Question
Discussion stats
  • 2 replies
  • 147 views
  • 0 likes
  • 1 in conversation