Help using Base SAS procedures

data set export from sas to import into RDBMS.

Reply
Frequent Contributor
Posts: 110

data set export from sas to import into RDBMS.

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?

Respected Advisor
Posts: 3,156

odbc doubt.

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

Haikuo

Frequent Contributor
Posts: 110

data set export from sas to import into RDBMS.

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 .

Super User
Posts: 5,429

data set export from sas to import into RDBMS.

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.

Data never sleeps
Frequent Contributor
Posts: 110

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

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.

Frequent Contributor
Posts: 110

data set export from sas to import into RDBMS.

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.

Super User
Posts: 10,028

data set export from sas to import into RDBMS.

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

Ask a Question
Discussion stats
  • 6 replies
  • 180 views
  • 0 likes
  • 4 in conversation