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?
More detail will definitely help. For now, it seems to me that 'update' statement using with datastep or SQL may be relevant.
Haikuo
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 .
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.
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.
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.
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.