BookmarkSubscribeRSS Feed
QLi
Fluorite | Level 6 QLi
Fluorite | Level 6

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

 

10 REPLIES 10
PaigeMiller
Diamond | Level 26

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;
--
Paige Miller
QLi
Fluorite | Level 6 QLi
Fluorite | Level 6
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
Pyrite | Level 9

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
Super User
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
Super User
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
Fluorite | Level 6 QLi
Fluorite | Level 6
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
Super User
libname xls?
DId you try PCFILES or ODBC?
Tom
Super User Tom
Super User

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
Super User

@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
Super User

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 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 7826 views
  • 1 like
  • 7 in conversation