topic Re: export 2 datasets data to only one Excel file in SAS Programming

export 2 datasets data to only one Excel file

Gab12 — Wed, 15 Mar 2023 15:49:44 GMT

Hello ,

I have two datasets say DS_A and DS_B .

Both datasets contains 3 variables & 10 observations each.

I wish to export these datasets data to only one Excel file ( File_name : Sample.XLS, SheetName: Sheet1 )

I need to Export DS_A data to Sheet1 from A1:C10 cells and DS_B data to Same Sheet1 from A15:C25 cells .

thank you for proposing an efficient solution that would allow me to export these two tables without overwriting the first export
Thank you in advance for your help

Re: export 2 datasets data to only one Excel file

Tom — Wed, 15 Mar 2023 17:00:00 GMT

Why? Why would use make an XLS file in this day and age? WHy would you put data from two different sources into one sheet of a spreadsheet?

If you want the two datasets as one then just combine them.

libname out xlsx 'sample.xlsx';
data out.sheet1;
  set ds_a ds_b;
run;

If you want to produce a report you can use the SHEET_INTERVAL=NONE option of ODS EXCEL

ods excel file='sample.xlsx'
  option( sheet_interval=none sheet_name='Sheet1')
;

proc print data=ds_a noobs;
run;
proc print data=ds_b noobs;
run;

ods excel close;

Re: export 2 datasets data to only one Excel file

Gab12 — Wed, 15 Mar 2023 19:02:43 GMT

Hello thank you for your answer The two databases have different structure and data I want to export them in the two ranges indicated in my question on the same sheet the problem is that if we specify the ranges in PROC EXPORT it crase the other data that already exists in the sheet

Re: export 2 datasets data to only one Excel file

Tom — Wed, 15 Mar 2023 19:38:32 GMT

@Gab12 wrote:
Hello thank you for your answer The two databases have different structure and data I want to export them in the two ranges indicated in my question on the same sheet the problem is that if we specify the ranges in PROC EXPORT it crase the other data that already exists in the sheet

That is not how you export data to XLSX files. Write the data to their own sheets. If you want then copy it into specific places on another sheet to make a "pretty" report then use Excel's internal functionality.

Or just create the whole report using SAS and skip the Excel programming completely.