topic data set export from sas to import into RDBMS. in SAS Procedures

data set export from sas to import into RDBMS.

sassharp — Thu, 01 Mar 2012 20:13:30 GMT

need to save sas data set in database by using libname. at the starting of each month need to delete only( said only) rows with out deleting table structure and update the table with new data.

any ideas?

odbc doubt.

Haikuo — Thu, 01 Mar 2012 20:53:49 GMT

More detail will definitely help. For now, it seems to me that 'update' statement using with datastep or SQL may be relevant.

Haikuo

data set export from sas to import into RDBMS.

sassharp — Thu, 01 Mar 2012 21:32:06 GMT

proc sql; delete from import.x; quit;

data import.x;

set export.y;

run;

import and export are libnames defined correctly on oracle, sas side respectively.

Q) Need to delete all rows in x and fill rows with y .

data set export from sas to import into RDBMS.

LinusH — Fri, 02 Mar 2012 08:05:27 GMT

The data step replaces the RDBMS table, so in your example the delete operation is unnecessary.

If you want the table to be persistant, use delete, then SQL insert (or proc append).

Also, there are perhaps more efficient truncation command for your RDBMS that can be executed in SQL pass-thru.

Re: data set export from sas to import into RDBMS.

sassharp — Fri, 02 Mar 2012 14:45:53 GMT

I do know sql pass thru. Here I wanted to delete all previous months data then load the RDBMS table with present month data(from SAS). once I use proc delete whole RDBMS table is deleting. Just I wanted to delete rows only. not table structure.

data set export from sas to import into RDBMS.

sassharp — Fri, 02 Mar 2012 18:33:00 GMT

proc sql;

connect to sqlsvr(dsn=xxx user=yyy pwd='zzz');

execute (truncate table base.monthlydata) by sqlsvr;

disconnect from sqlsvr;

quit;

error in log

ERROR: CLI execute error: [DataDirect][ODBC SQL Server Driver][SQL Server]Cannot find the object "monthlydata" because it does not

exist or you do not have permissions.

data set export from sas to import into RDBMS.

Ksharp — Mon, 05 Mar 2012 06:55:45 GMT

Just as LinusH said in your example the delete operation is unnecessary if the final table name is the same with old table.

data class;
 set sashelp.class;
run;
proc sql;
 delete * from class;

 insert into  class
  select * from sashelp.class;
quit;

Ksharp