BookmarkSubscribeRSS Feed
deleted_user
Not applicable
Hi,

Any one knows how to export different data sets in to one Excel file in different sheets
its urgent plz if anyone knows the answer
thanks
srinu
4 REPLIES 4
Cynthia_sas
SAS Super FREQ
The method that you use for this will depend on whether you're using PROC EXPORT or the LIBNAME engine for Excel and whether you have SAS/Access for PC File Formats installed.

Your best bet for help with this task is to contact Tech Support.

cynthia
MarkRoosen
Fluorite | Level 6
Hi,

if you do not have any licenses for export to Excel you can create XML files that Excel can reads (with multiple sheets)
Basic information you can find on
http://support.sas.com/rnd/base/topics/odsmarkup/tableeditor/index.html

Remark: for a lot of data these files will be huge ! !

a quick example could be: ( you need the file tableeditor.tpl' that you can find in the URL mentioned above
=============================================================================

%include 'c:\temp\tableeditor.tpl';
ods tagsets.tableeditor file="c:\temp\export_naar_excel.html"
style=styles.mystyle
options(excel_zoom="90"
excel_scale="90"
excel_autofilter="yes"
excel_frozen_headers="yes"
excel_orientation="landscape"
sheet_name="first,second,third"
excel_table_move="1,2,3"
auto_format="color1"
excel_save_file="c:\\ temp.xls"
);
proc print data=sashelp.class;
run;
proc report data=sashelp.class nowd;
run;
proc print data=sashelp.orsales;
run;
ods tagsets.tableeditor close;



best regards,
Mark Roosen
Kwok
Calcite | Level 5
Here is an exmaple I always use:

Libname in ;

Filename odsout

ods listing close; run;
ods tagset.excelXP file=odsout ;
proc print data=in.one ;
run;
ods tagset.excelXP ;
proc print data=in.two;
Run;
ods tagset.excelXP ;
proc report data=in.three;
run;
....
ods _all_ close; run;
ods losting; run;
The trick is for each seperate sheet, you need a ods statement. However, only the 1st ods statement define the output( file=odsout). If every ods statment has a file statement, then it will be overwritten by the next ods with (file=).
If you want each sheet to have a different sheet name, then use the sheet_name="" as the ODS option.

Hope this help.

Regards
David
deleted_user
Not applicable
I am also trying to move data from SAS into an Excel 2003 workbook. The workbook is pre-defined and fairly complex. The current process uses ADO and VBA code to populate the data. This process is slow on our server , si I have been experimenting with alternates. I would need to populate defined Excel named ranges and am trying to avoid the R1C1 format for flexability.

The Excel Libname Engine sounds like it should work, but is corrupting the workbook. Here is the sample code.

Libname XLSLib clear;
Libname XLSLib Excel "&XLSFile2." SCAN_TEXT=NO Header=NO VER=2002;

Proc SQL NOPRINT;

Create Table Work.Tmp99 as
Select 0 as F1
From SD.Dual as dTHRw
Union All
Select 0 as F1
From SD.Dual as dDDDW
Union All
Select Count(*) as F1
From Work.Tmp01
Union All
Select Count(*) as F1
From Work.tmp02
Union All
Select Count(*) as F1
From Work.tmp03

Quit;

Proc append
Base = XLSLib.Counters
Data = Work.Tmp99;
Run;

Libname XLSLib clear;

Any ideas? Is the Range option available in ODS?



Thanks,
Tom

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 4 replies
  • 928 views
  • 0 likes
  • 4 in conversation