BookmarkSubscribeRSS Feed
chennupriya
Quartz | Level 8

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

9 REPLIES 9
Reeza
Super User
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.
chennupriya
Quartz | Level 8

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

chennupriya
Quartz | Level 8

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

Reeza
Super User

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.

 

Patrick
Opal | Level 21

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.

 

chennupriya
Quartz | Level 8

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;

Patrick
Opal | Level 21

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

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 1892 views
  • 0 likes
  • 3 in conversation