topic Re: SAS EG 7.15 - How do I Export SAS Dataset and Append to Excel File with Same Structure in New SAS User

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

RADAGBE9 — Sat, 26 Oct 2019 01:39:40 GMT

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

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

SASKiwi — Sat, 26 Oct 2019 02:07:44 GMT

Appending isn't possible using just SAS: https://documentation.sas.com/?docsetId=acpcref&docsetTarget=n0cfqhcraieg81n1ier2cpp87fnz.htm&docsetVersion=9.4&locale=en

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.

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

Kurt_Bremser — Sat, 26 Oct 2019 09:37:29 GMT

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

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

art297 — Sat, 26 Oct 2019 16:37:49 GMT

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

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

RADAGBE9 — Sat, 26 Oct 2019 19:44:09 GMT

Thanks. I'll give it a try.

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

RADAGBE9 — Sat, 26 Oct 2019 23:26:08 GMT

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.