topic Re: update table in sql server management studio from SAS dataset in SAS Programming

update table in sql server management studio from SAS dataset

chennupriya — Fri, 06 Nov 2015 15:39:18 GMT

I have a sas dataset finalxyz so its has data from aug2014 to aug2015 now and if i run next month it will have sep2015 records

and using libname i have loaded that table into sql server but every month we get records for new months so there is a way that if i run sas code the table would get updated automatically with new records in sql ?. Can anyone pls help

Thanks

Re: update table in sql server management studio from SAS dataset

Reeza — Fri, 06 Nov 2015 15:41:42 GMT

Can you assume the old records won't have changed and you only need to upload the new records or the does the whole table need to be updated?

Anyways look at the UPDATE statement in a Datastep. Assuming you have the correct permissions on the server you should be able to run an update automatically.

Re: update table in sql server management studio from SAS dataset

chennupriya — Fri, 06 Nov 2015 15:53:48 GMT

in old records some amount fields get void and loan numbers may change and also we need to upload new records

Re: update table in sql server management studio from SAS dataset

Reeza — Fri, 06 Nov 2015 16:03:43 GMT

Sounds like an UPDATE to me:

http://support.sas.com/documentation/cdl/en/basess/58133/HTML/default/viewer.htm#a001329151.htm

Re: update table in sql server management studio from SAS dataset

chennupriya — Fri, 06 Nov 2015 16:22:29 GMT

how to write a code so that it will update the sql table in sql server when i run sas code both sas dataset and sql table will have same variables but in sas dataset which i run everymonth will have any changes in old records and new records

can u pls let me know how to write the code

Thanks

Re: update table in sql server management studio from SAS dataset

Reeza — Fri, 06 Nov 2015 16:40:19 GMT

http://analytics.ncsu.edu/sesug/2001/P-354.pdf

Here is a paper that goes over some of the options.

In addition to Update, there's Modify and a straightforward Merge.

Re: update table in sql server management studio from SAS dataset

Patrick — Sat, 07 Nov 2015 02:20:00 GMT

You need to update data already loaded and you need to insert new records not loaded yet. Your source is a SAS data set your target is a SQL Server table.

What you're after is an "upsert". There are multiple way of how to do this.

Option1:

Use the MODIFY statement in a SAS dataset

Option2:

Split up your data. Run an UPDATE for data previously loaded (I would use the SQL UPDATE), use a Proc Append for new data.

Option3:

- Load your data into a staging table (just an empty table which you create on the server).

- Load all data into this staging table using Proc Append (eventually with bulk load options),

- then via pass-through SQL execute a MERGE statement https://technet.microsoft.com/en-us/library/bb522522(v=sql.105).aspx

- truncate your staging table (or leave the data there for "debugging" and only truncate it the next time before you load new data)

Even though it sounds a bit more complicated I personally would go for Option 3 if the data volumes get into the millions.

Re: update table in sql server management studio from SAS dataset

chennupriya — Tue, 17 Nov 2015 15:38:30 GMT

Hi ,

Could you pls explain the code for option 2 . actually I have used below code but when i am using this its chaging teh date format

pls explain how to do it

proc sql;
delete from sqldb.xy;
insert into sqldb.xy
select * from price.yz
;
quit;

Re: update table in sql server management studio from SAS dataset

chennupriya — Tue, 17 Nov 2015 20:13:42 GMT

Thank you

Re: update table in sql server management studio from SAS dataset

Patrick — Wed, 18 Nov 2015 21:55:27 GMT

@chennupriya

The "changing the date format" is another issue and you will have to explain a bit more what you have and what you get - and what you would expect to get.

For the SAS/Access engines to get it right best use for the source SAS variables containing date values: date9., datetime20.,

http://support.sas.com/documentation/cdl/en/acreldb/68028/HTML/default/viewer.htm#n13gtugcxgdqstn1pooivt622n4q.htm

As for your code:

If all you need is an exact copy of the current data set from SAS in SQL Server then depending on data volumes your approach is eventually very o.k. Does this run within reasonable time?

Normally a SQL DELETE is a slow operation and if it's about removing all data then a TRUNCATE statement (executed in a Pass-through block) is much faster.

http://www.techonthenet.com/sql_server/truncate.php

http://support.sas.com/documentation/cdl/en/acreldb/68028/HTML/default/viewer.htm#p09s44hpea09stn1puyx1l5vcwwt.htm

(do you use the SAS Access to ODBC or SAS Access to SQL Server?)

For the insert: Instead of the SQL Insert you could also use Proc Append

proc append base=sqldb.xy data=price.yz;
run;

There are also a few options you might want to look up if useful for you like:

dbcommit

insertbuff

http://support.sas.com/documentation/cdl/en/acreldb/68028/HTML/default/viewer.htm#n009p6849p6ynzn1t76wskgry4ob.htm

http://support.sas.com/documentation/cdl/en/bidsag/68193/HTML/default/viewer.htm#p1icta556r8bacn1ns9kvvnxd4iv.htm