- I have a huge excel file containing several years worth of data in a file called: C:\MyData\Basefile.xlsx.
- I also have a daily scheduled job in SAS EG which creates one day's worth of sas dataset called newdata.
- Both files have the same variables in the same order.
How can I Export & Append the newdata to Basefile.xlsx without necessarily importing Basefile.xlsx into SAS.
Thanks in Advance.
Appending isn't possible using just SAS: https://documentation.sas.com/?docsetId=acpcref&docsetTarget=n0cfqhcraieg81n1ier2cpp87fnz.htm&docset...
It is only possible if you write your daily dataset to a temporary sheet in Excel and then separately use an Excel process (macro or vbscript?) to copy these rows to the end of your permanent sheet.
Please remember Excel isn't a database and so a lot of database functionality like data appends and selective inserts and deletes simply aren't possible.
Keep your data in SAS, do the append there, and then export the whole to Excel.
Also consider if what you do in Excel can't be also done with SAS. SAS is much more stable and scales much farther than Excel (sooner or later you'll hit the size limits of Excel anyway).
If I do the Append in SAS, how do I replace the first appended file with the new data the following day? It will not replace the first BASE file because it is already in use.
Like @SASKiwi said, I don't think it can be done without a macro and/or vb script. However, a group of us wrote a macro a couple of years ago that does provide most of the functionality you'd need. That said, you'd have to test it to make sure it will work on your version of EG. You can download the macro and Global Forum paper at: http://www.sascommunity.org/wiki/Excelling_to_Another_Level_with_SAS
The macro wasn't designed to append to the end of an existing file but, rather, to append to a range beginning at a cell that you can specify. Thus, if you can maintain a file that only has one variable and value (namely the next available row in your basefile Excel file), you could easily put that value into a macro variable and use that macro variable for the macro's RANGE parameter.
Art, CEO, AnalystFinder.com
Thanks. I'll give it a try.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.