data set export from sas to import into RDBMS.

[sassharp]

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?

[Haikuo]

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

Haikuo

[sassharp]

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 .

[LinusH]

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.

[sassharp]

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.

[sassharp]

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.

[Ksharp]

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