How to edit, delete, and append records on SQL Server

[holcoje]

Long time SAS user but new to SQL Server. Current project requires I make edits of records, deleting records, and appending records to existing tables in a SQL Server database. I have a LIBNAME connection to the database, and can read in tables to SAS no problem. Will I be able to do my edits, deletes, and appends to the tables with the normal SAS DATA step and other PROCEDURES? My first thought was to just bring the table into SAS, make all necessary edits, deletes, and appends, then replace that table in SQL Server with the SAS table, but I've been told I am not allowed to delete and create tables in SQL Server, only make changes to the existing tables. 

[SASKiwi]

We do this a lot and have found PROC DATASETS APPEND is good for adding rows, and SQL DELETE is good for removing rows. We don't normally do modifies as they can get complicated. We find it easier to delete rows then reappend them as it's a lot simpler and our data volumes aren't very large so performance is still good. 

[Ksharp]

If you do not know the SQL syntax of SQL Server, you could try PROC SQL of SAS ,which also have these features .

1) edit a table ,try
proc sql;
update sqlserver.mytable
set sex='F'
where ......
quit;

2)delete records,try:
proc sql;
delete from sqlserver.mytable where sex='F';
quit;

3)append recorder,try:
proc sql;
insert into sqlserver.mytable
values(1,'F',......);
quit;


For more details information check the doc of PROC SQL.
you could find it in
https://support.sas.com/

[holcoje]

I am familiar with PROC SQL. But in order to make those changes directly to the SQL Server DB, I would need to connect to the table with a passthrough, correct? For security, operations is not providing is with the username and password for the SQL Server database. We have a continuous connection as a LIBNAME statement.

[LinusH]

A LIBNAME connection will probably suffice for most situations.
Try it, and than return if you encounter any issues, like performance.

[LinusH]

And if you need explicit pass through, you can try out the CONNECT USING construct.

[Patrick]

@holcoje Your question and later comments make me believe that you are very familiar with SAS Proc SQL included but that interfacing with a database (not only SQL Server) is new to you.

If so then here a few pointers:

1. If you have a libref that points to a database then you can use normal SAS data step code and Proc's including Proc SQL in the SAS flavour to interface with this database.

2. You use SAS datastep code normally only to READ a db table. The DATA statement is always replacing tables and that's not how databases work.

3. To INSERT rows from a SAS table into a database table Proc Append performs really well and is what often gets used.

4. To UPDATE or DELETE rows from a database table you normally use SQL code via Proc SQL

5. The SQL code does not need to be explicit pass-through. SAS will convert the SAS SQL flavor to DB SQL flavor as far as possible.

- This only works if you don't use SAS functions in your SQL that can't get converted to native database SQL

- For each database (SAS/Access module) it's documented which functions are not supported.

- The following options will create SAS Log that tells you what SAS could push to the database for execution: options sastrace=',,d,' sastraceloc=saslog nostsuffix;

6. Explicit SQL (using the connect to statement) allows you to write SQL in the DB flavor. SAS will then just send this SQL to the database "as is".

- if you have already an existing libref for the database then you can use syntax like: CONNECT USING <libref>. You don't need to define/provide a new connection with all the info.

- Explicit SQL executes fully in-database. It "knows" nothing about SAS. What this means is that also your source data for INSERT or UPDATE needs to exist on the database. If your source data is in SAS then you would first need to load this data into another table (like a staging table) in the database using for example Proc Append and only then you could write explicit SQL which uses this database table to update/insert into a target database table.

- Using explicit pass-through SQL has the advantage that you can use DB SQL extensions that are not available in SAS SQL syntax (like windowing function/partition by clauses). This of course requires that you familiarize yourself with the DB specific SQL.

When interfacing with a database: Always try to minimise data movement between SAS and the database. This is the bottleneck in regards of performance and something you always need to take into consideration.

Most of the time explicit SQL gets used for reading data from a DB into SAS. Using explicit SQL allows for DB specific syntax that can take advantage of DB functionality that doesn't exist in SAS SQL syntax - to subset and/or aggregate data/reduce data volumes before transfering the data to the SAS side for further processing.

...I hope all of the above made sense to you and will be helpful. Interfacing with a database needs some upskill time but once you've got it most of the "principles" are the same for any database.