Uploading data to ORAtable from SAStable

[karlomutschler]

Hi all,
I have created a SAStable(origtable) from an excel table using
PROC IMPORT.

Now I would like to replace the data in an ORAtable(desttable)
with the data from the SAStable.

I can't use DBLOAD, because the varnames are longer than 8 chars.

Any help is much appreciated.
Kind regards.
Karlo


meistertools@gmx.net

 

[klaz2002]

Karlo,

Here is an example
proc sql;
connect to oracle(user="&uname" password="&pw" path="db_path" buffsize=200);
create table TABLENAME as
select * from Your_table;
disconnect from oracle;
quit;

Hope this helps.

PS you can also use the ODBC engine in the libname statement.

Klaz

 

[karlomutschler]

Thank you Klaz for your valued reply.

1. I have rights to create a SAS table and have done so using
PROC IMPORT from an excel table.
2. I have NO rights to create an ORA table - it has already been
and contains data which needs to be REPLACED with the data from
the SAS table.

Leaning on

http://www.csc.fi/cschelp/sovellukset/stat/sas/sasdoc/sashtml/accdb/z1234400.htm

I have the following:
libname origtable;
libname desttable oracle user="&user" password="&pword" path="&oradwh";

proc sql;
(BULKLOAD=YES BL_LOAD_METHOD=REPLACE)
select * from origtable;
quit;

Any assistance greatly appreciated
Regards Karlo

meistertools@gmx.net

 

[klaz2002]

Have you tried the SET statement (in SQL)? This may be a round-about way to modify the table. I believe that SQL can modify a record via the SQL set statement. What you can do is have a data step 'write' these statement out to a text file and then include the text file back in.
Here is the example:
%let datfile = c:\test.txt;
filename fref "&datfile";
data test;
set your table end=last;
file fref;
put "set var1='" a_char_var "'";
*** REPEAT THE SET STATEMENTS AS NEEDED;

put "where your-by_var='" char_var_id "';";
run;

proc sql;
connect to oracle(user info........)
update table_name....;
%include "&datfile";
quit;

I hope you get the picture. Let me know if it works.
Klaz

 

[karlomutschler]

Hi all,
this is the solution that works for me:

PROC IMPORT OUT=orig_data-set
DATAFILE= "C:\path\filename.xls"
SHEET="worksheet"
DBMS=EXCEL2000 REPLACE;
GETNAMES=YES;
RUN;

/*===Renaming a Variable at Time of Input===*/
DATA orig_data-set_mod;
SET orig_data-set(RENAME=
(orig_column1_name=col1name
orig_column2_name=col2name
orig_column3_name=col3name
orig_column4_name=col4name
)
)
;
RUN;

PROC DBLOAD DBMS = ORACLE DATA = orig_data-set_mod APPEND;
user = "&dwhusr";
password = "&dwhpwd";
path = "@dwhcust";
table = dwhpers.Oracle-Rdb-table-name;
LABEL;
LIMIT = 0;
COMMIT = 500000;
RESET ALL;
LIST;
LOAD;
RUN;

meistertools@gmx.net