03-01-2012 03:13 PM
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.
03-01-2012 04:32 PM
proc sql; delete from import.x; quit;
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 .
03-02-2012 03:05 AM
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.
03-02-2012 09:45 AM
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.
03-02-2012 01:33 PM
connect to sqlsvr(dsn=xxx user=yyy pwd='zzz');
execute (truncate table base.monthlydata) by sqlsvr;
disconnect from sqlsvr;
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.
03-05-2012 01:55 AM
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;