11-06-2015 10:39 AM
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
11-06-2015 10:41 AM
11-06-2015 11:22 AM
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
11-06-2015 11:40 AM
Here is a paper that goes over some of the options.
In addition to Update, there's Modify and a straightforward Merge.
11-06-2015 09:17 PM - edited 11-06-2015 09:20 PM
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.
Use the MODIFY statement in a SAS dataset
Split up your data. Run an UPDATE for data previously loaded (I would use the SQL UPDATE), use a Proc Append for new data.
- 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.
11-17-2015 10:38 AM
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
delete from sqldb.xy;
insert into sqldb.xy
select * from price.yz
11-17-2015 03:57 PM - edited 11-18-2015 04:55 PM
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.,
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.
(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: