DATA Step, Macro, Functions and more

update table in sql server management studio from SAS dataset

Reply
Frequent Contributor
Posts: 125

update table in sql server management studio from SAS dataset

Hi

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

Super User
Posts: 19,789

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

Posted in reply to chennupriya
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.
Frequent Contributor
Posts: 125

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

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

Super User
Posts: 19,789

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

Posted in reply to chennupriya
Frequent Contributor
Posts: 125

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

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

Super User
Posts: 19,789

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

Posted in reply to chennupriya

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.

 

Respected Advisor
Posts: 4,173

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

[ Edited ]
Posted in reply to chennupriya

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.

 

Frequent Contributor
Posts: 125

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

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;

Respected Advisor
Posts: 4,173

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

[ Edited ]
Posted in reply to chennupriya

@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#n13gtugcxgdqstn1po...  

 

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#p09s44hpea09stn1pu...

(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#n009p6849p6ynzn1t7...

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

 

Frequent Contributor
Posts: 125

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

Thank you 

Ask a Question
Discussion stats
  • 9 replies
  • 693 views
  • 0 likes
  • 3 in conversation