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
in old records some amount fields get void and loan numbers may change and also we need to upload new records
Sounds like an UPDATE to me:
http://support.sas.com/documentation/cdl/en/basess/58133/HTML/default/viewer.htm#a001329151.htm
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
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.
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.
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;
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.
http://www.techonthenet.com/sql_server/truncate.php
(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
Thank you
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.