SAS EG 7.15 - How do I Export SAS Dataset and Append to Excel File with Same Structure

[RADAGBE9]

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

[SASKiwi]

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.

[Kurt_Bremser]

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

[RADAGBE9]

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.

[art297]

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

[RADAGBE9]

Thanks. I'll give it a try.