topic Re: How to export to same excel sheet without overwriting? in SAS Programming

How to export to same excel sheet without overwriting?

jerrylshen — Wed, 24 Jul 2019 16:58:20 GMT

I'm importing the data from .xlsx and using proc sql to create/manipulate the data in SAS 9.4.

This is how I'm currently exporting to .xlsx but this overwrites that particular sheet. I want to reuse the same sheet.

proc export
DATA = data_name
DBMS = xlsx 
OUTFILE = "C:\\...\outfile.xlsx"
REPLACE;

sheet=sheet_name;
run;

I guess one alternative would be to do all the data manipulation and then do a single export as opposed to data manipulation then export, data manipulation then export, data manipulation then export, and so on. But is there a way to export without overwriting? I tried looking this up and nothing relevant came up.

Thanks

Edit: I want different sets of data on the same sheet one below the other, sort of like appending

Re: How to export to same excel sheet without overwriting?

Reeza — Wed, 24 Jul 2019 15:32:33 GMT

Not via XLSX afaik. You can with PCFILES though, but then you have some other limitations unfortunately. First you do need to drop that sheet and then re-add it so not sure that will work, if you want other information to remain on the sheet.

Re: How to export to same excel sheet without overwriting?

ballardw — Wed, 24 Jul 2019 16:13:06 GMT

Do you want to place to different sets of data on the same sheet one below the other?

Or are you expecting to have this behave as a single block of data just adding lines without a header row?

Export in not the most flexible tool as it is pretty much intended to create one output file for one data set.

You might be able to accomplish what you want with ODS Excel as that will allow multiple procedures to send ODS output, such as Proc Print, to the same or different sheets depending on options like Sheet_interval.

Re: How to export to same excel sheet without overwriting?

jerrylshen — Wed, 24 Jul 2019 17:00:25 GMT

Alright, thanks for the info, I'll take a look into ODS Excel

Re: How to export to same excel sheet without overwriting?

Reeza — Wed, 24 Jul 2019 17:11:18 GMT

ODS EXCEL does assume that you can create an entirely new workbook each time you run this process. It does not have the option of adding sheets to an already existing workbook.

Re: How to export to same excel sheet without overwriting?

art297 — Wed, 24 Jul 2019 17:36:25 GMT

If you're on Windows then take a look at the exportxl macro. I think it can do everything that you described. You can download it from:

http://www.sascommunity.org/wiki/Excelling_to_Another_Level_with_SAS

Art, CEO, AnalystFinder.com