topic Re: How to export multiple SAS Datasets into the same sheet in Excel workbook? in SAS Programming

How to export multiple SAS Datasets into the same sheet in Excel workbook?

QLi — Thu, 07 Nov 2019 16:26:42 GMT

I have multiple sas small datasets and need to export the same sheet in Excel workbook. I defined different range cells for each data set in the same sheet. Anyone know how to do this ?

Thanks in advance

Re: How to export multiple SAS Datasets into the same sheet in Excel workbook?

PaigeMiller — Thu, 07 Nov 2019 16:28:38 GMT

One way (but you have no control over the exact positioning of the tables)

ods excel file="myexcelfile.xlsx" options(sheet_interval='NONE');
proc print data=dataset1;
run;
proc print data=dataset2;
run;
ods excel close;

Re: How to export multiple SAS Datasets into the same sheet in Excel workbook?

QLi — Thu, 07 Nov 2019 16:37:16 GMT

Thanks for your quick response.
ODS is one way. However its generated file size so big. I need to export sas datasets in one template sheet which has specific position for each data set. Thanks

Re: How to export multiple SAS Datasets into the same sheet in Excel workbook?

Krueger — Thu, 07 Nov 2019 16:41:18 GMT

Not sure that this is the "best" approach by any means but I've had to setup a powershell script to re-organize the data before (mainly copy-pasting into macro-enabled workbook). If all else fails you can look into this approach.

Re: How to export multiple SAS Datasets into the same sheet in Excel workbook?

Reeza — Thu, 07 Nov 2019 16:44:32 GMT

From ODS EXCEL? I recall that issue with ODS TAGSETS but not ODS EXCEL.

What version of SAS do you have exactly? DDE is one option as is the custom macro written by some users here or you can try exporting to a named range.

First copy your template so you don't mess it up, then assign a libname to the file and see if the named ranges show up as tables - they should. You may want them to have some data to examine it.
If they are there then you can use a data step or proc sql to replace the tables such as:

proc sql;
drop table mywb.named_range1;
quit;

data mywb.named_range1;
set have1;
run;

Note that when you do export data, the formats and such will likely disappear and variable names are always exported so what I usually do is export my data to a sheet and then link it to the range I want to make my life a lot easier.

Re: How to export multiple SAS Datasets into the same sheet in Excel workbook?

Reeza — Thu, 07 Nov 2019 16:45:54 GMT

This isn't a great standalone presentation but the code is in here that demonstrates the steps needed. The DDE is for running custom macros and if I was doing it today I wouldn't necessarily do it that way.

Re: How to export multiple SAS Datasets into the same sheet in Excel workbook?

QLi — Thu, 07 Nov 2019 17:55:45 GMT

I am using EG7.1, which should with SAS9.4. I wrote the following codes. it can not export the same sheet, each data set automatically generated different sheet.

options noxwait xsync;
x copy "temp_v0.xlsx" &excel_out;
libname xls excel &excel_out ; run;

proc datasets lib = xls nolist;
delete
Summary Rejected1 Rejected2 Point_O_New Point_overall_Existed ;
quit;

data xls.Summary; set RDP_Sum_t ; run;
data xls.Rejected1; set RDP_Sum_Reject ;run;
data xls.Rejected2; set RDP_Sum_Reject2 ; run;
data xls.Point_overall_Existed ; set Point_1(where=( EXISTINGCUSTOMER='true')); run;

libname xls clear;

I will try your approach. Thank you very much

Re: How to export multiple SAS Datasets into the same sheet in Excel workbook?

Reeza — Thu, 07 Nov 2019 18:19:48 GMT

libname xls?
DId you try PCFILES or ODBC?

Re: How to export multiple SAS Datasets into the same sheet in Excel workbook?

Tom — Thu, 07 Nov 2019 20:07:51 GMT

How exactly are you going dump multiple datasets into the same worksheet? If the columns are not the same it will look terrible. If they are the same then why not just combine them into one dataset and dump that?

Re: How to export multiple SAS Datasets into the same sheet in Excel workbook?

Ksharp — Fri, 08 Nov 2019 13:25:20 GMT

I remember there is an option :

ods excel file="myexcelfile.xlsx" options(sheet_interval='NONE' start_at='2,5');

You could search it at blogs.sas.com

Re: How to export multiple SAS Datasets into the same sheet in Excel workbook?

ballardw — Fri, 08 Nov 2019 23:35:33 GMT

@QLi wrote:
I am using EG7.1, which should with SAS9.4. I wrote the following codes. it can not export the same sheet, each data set automatically generated different sheet.

options noxwait xsync;
x copy "temp_v0.xlsx" &excel_out;
libname xls excel &excel_out ; run;

proc datasets lib = xls nolist;
delete
Summary Rejected1 Rejected2 Point_O_New Point_overall_Existed ;
quit;

data xls.Summary; set RDP_Sum_t ; run;
data xls.Rejected1; set RDP_Sum_Reject ;run;
data xls.Rejected2; set RDP_Sum_Reject2 ; run;
data xls.Point_overall_Existed ; set Point_1(where=( EXISTINGCUSTOMER='true')); run;

libname xls clear;

I will try your approach. Thank you very much

That is not actually "exporting" it is "writing", at least in my mind. Excel is a poor data interchange medium and since data steps write data then SAS has, rightly in my mind, forced writing data sets to spread sheets as separate pages so that you cannot in any way, shape, or form have inconsistent data in a single column.

Nothing like reading the first 600 rows of data as "dates" and then discover at row 602 the data is now "names", phone numbers or similar.

And if the variables are all of the same type and name then combine the SAS data sets and use a single process to write the data.

You could even add a variable as those sets are combined to have information about the data source on each and every record. Your attempted approach does not do that. So even if you succeeded how do you know where the Reject data starts?