- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- 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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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).
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Thanks. I'll give it a try.