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

[QLi]

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

[PaigeMiller]

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;

[QLi]

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

[Krueger]

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.

[Reeza]

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.

[Reeza]

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.

[QLi]

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

[Reeza]

libname xls?
DId you try PCFILES or ODBC?

[Tom]

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?

[ballardw]

---

@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?

[Ksharp]

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