BookmarkSubscribeRSS Feed
raveena
Obsidian | Level 7
Hi,

Need to export multiple data set to one excel file in different sheet.

By using DDE, We did formatting for sheet 1, the same format should appear for sheet 2, sheet 3. When I use the sheet option in DDE, it over-ride the previous sheet then create the last sheet, all other previous sheets are empty.

So I tried to use proc export, the data is getting exported but am not getting the same format style (need lables for variables and should be bold) as sheet 1.

Please let me know.

Thanks in Advance.
5 REPLIES 5
Cynthia_sas
SAS Super FREQ
Hi:
Using DDE is not an ODS or Base Reporting procedure question. Neither is using PROC EXPORT. However, you have found the right place, IF you want to use ODS and IF you want to create multi-sheet workbooks with some amount of style and formatting (such as bold, specific fonts, colors, etc), you can use ODS TAGSETS.EXCELXP.

To impact the overall look and feel, a SAS style option will help -- I have used the ANALYSIS style -- then in the last sheet, I use a PROC PRINT STYLE= override to change the foreground color of the headers to purple. The SHEET_LABEL suboption is used in the ODS TAGSETS.EXCELXP statements to name each sheet for each procedure's output. Since ODS TAGSETS.EXCELXP destination is self-documenting, I also used the DOC='HELP' suboption so that you can look in the SAS log to see the kinds of suboptions available to you. This paper also outlines other suboptions that are useful:
http://www.nesug.org/proceedings/nesug08/ap/ap06.pdf

For help with DDE and/or PROC EXPORT, you can perform a Google Search looking for user-group papers on the subject of SAS and DDE or PROC EXPORT or post your question in the forum for SAS DATA Step questions (since using DDE involves using the DATA step) or the SAS general procedures forum (since PROC EXPORT is a general procedure and not a reporting procedure). For help with either DDE or PROC EXPORT, you can also open a track with Tech Support.

cynthia
[pre]
ods listing close;
ods tagsets.excelxp file='c:\temp\makemult.xls' style=analysis
options(doc='Help' sheet_label='Wombat');

proc print data=sashelp.class label noobs;
var name height weight age sex;
label name = 'The Name'
age = 'How Old';
run;

ods tagsets.excelxp options(sheet_label='Koala');

proc print data=sashelp.cars(obs=5) label noobs;
var make type model origin msrp invoice;
label make = 'Vehicle Make'
type = 'Category'
model = 'Vehicle Model';
run;

ods tagsets.excelxp options(sheet_label='Eucalyptus');

proc print data=sashelp.shoes(obs=5) label noobs
style(header)={foreground=purple};
var region product sales inventory returns;
run;

ods tagsets.excelxp close;
[/pre]
raveena
Obsidian | Level 7
Hi Cynthia,

I tried this, but am getting an error

ods tagsets.ExcelXP path='&drv7.\NY\&project.\current'
file='test.xls'
style=analysis;
ods tagsets.ExcelXP options(embedded_titles='yes'
embedded_footnotes='yes');
run;
ods tagsets.ExcelXP options(sheet_name='age');
Proc Report data=summary_nj_b out=summary;
column curinv prev woked Newit rvvsrr;
define curinv / display 'CurrentInventory';
define prev / display 'Previous';
define woked / display 'Worked';
define Newit / display 'NewItems';
define rvvsrr / display 'Count';
run;
ods tagsets.ExcelXP close;
run;

Error Message:

2153 ods tagsets.ExcelXP path='&drv7.\NY\&project.\current'
2154 file='test.xls'
2155 style=analysis;
NOTE: Writing TAGSETS.EXCELXP Body file: test.xls
ERROR: Physical file does not exist, C:\Documents and
Settings\srajend\&drv7.\NY\&project.\current\test.xls.
WARNING: No body file. TAGSETS.EXCELXP output will not be created.
2156 ods tagsets.ExcelXP options(embedded_titles='yes'
2157 embedded_footnotes='yes');
WARNING: No body file. TAGSETS.EXCELXP output will not be created.
2158 run;
2159 ods tagsets.ExcelXP options(sheet_name='age');
WARNING: No body file. TAGSETS.EXCELXP output will not be created.
2160 Proc Report data=summary_nj_b out=summary;
2161 column curinv prev woked Newit rvvsrr;
2162 define curinv / display 'CurrentInventory';
2163 define prev / display 'Previous';
2164 define woked / display 'Worked';
2165 define Newit / display 'NewItems';
2166 define rvvsrr / display 'Count';
2167 run;

NOTE: The data set WORK.SUMMARY has 1 observations and 6 variables.
NOTE: There were 1 observations read from the data set WORK.SUMMARY_NJ_B.
NOTE: PROCEDURE REPORT used (Total process time):
real time 4.63 seconds
user cpu time 0.01 seconds
system cpu time 0.15 seconds
Memory 5607k


2168 ods tagsets.ExcelXP close;
2169 run;
Cynthia_sas
SAS Super FREQ
Hi:
Macro variable references (such as &DRV7 and &PROJECT) will NOT resolve correctly when enclosed in single quotes ('&DRV7' will not resolve, but "&DRV7" will resolve).

Your error has nothing to do with TAGSETS.EXCELXP. It has to do with the fact that &drv7 and &project did not resolve. Run the code below to see what I mean.

cynthia
[pre]
24 %let drv7 = abc_def;
25 %let project = something;
26
27 %put *** use single quotes ***;
*** use single quotes ***
28 %put '&drv7.\NY\&project.\current' ;
'&drv7.\NY\&project.\current'
29
30 %put *** use double quotes ***;
*** use double quotes ***
31 %put "&drv7.\NY\&project.\current" ;
"abc_def\NY\something\current"
[/pre]
raveena
Obsidian | Level 7
Cynthia,

I corrected the code as below and ran, am not getting any error. But i couldnt open the excel file.The excel file with the name test has been created on that particular path , but it gives an error as "unable to read file". I closed the proc report window also while opening that excel file.

ods listing close;
ods tagsets.ExcelXP path="&drv7.\NY\&project.\current"
file="test.xls"
style=analysis;
ods tagsets.ExcelXP options(embedded_titles='yes'
embedded_footnotes='yes');
run;
ods tagsets.ExcelXP options(sheet_name='age');
Proc Report data=summary_nj_b out=summary;
column curinv prev woked Newit rvvsrr;
define curinv / display 'CurrentInventory';
define prev / display 'Previous';
define woked / display 'Worked';
define Newit / display 'NewItems';
define rvvsrr / display 'Count';
run;
ods tagsets.ExcelXP close;
run;
ods listing;

Please let me know.
Cynthia_sas
SAS Super FREQ
Hi:
When I run this code (which uses SASHELP.CLASS):
[pre]
ods listing close;
ods tagsets.ExcelXP path="c:\temp"
file="test.xls"
style=analysis;
ods tagsets.ExcelXP options(embedded_titles='yes'
embedded_footnotes='yes');
run;
ods tagsets.ExcelXP options(sheet_name='age');
Proc Report data=sashelp.class nowd;
title 'The title';
footnote 'The footnote';
column name sex age height weight;
define name / display 'CurrentInventory';
define sex / display 'Previous';
define age / display 'Worked';
define height / display 'NewItems';
define weight / display 'Count';
run;
ods tagsets.ExcelXP close;
run;
ods listing;
[/pre]

I do get the output. In order to see the output, I have to click YES when I get the following message from Office/Excel 2010:

The file you are trying to open xxxxxxx.yyy is in a different format than specified by the file extension. verify the file is not corrupted and is from trusted source before opening the file. Do you want to open the file now?


This is an accurate message. When you use TAGSETS.EXCELXP, you are not creating a 'true, binary" Excel file. You are creating an Office 2003 Spreadsheet Markup Language XML file that Excel knows how to open and render. When you give the file an extension of ".xls", you are merely fooling the Windows registry into launching Excel when you double click on the file name. In order to open the file, you must click YES when you see this message. (as explained in this Tech Support note: http://support.sas.com/kb/35/581.html )

When I run my code, I see the following in the SAS log:
[pre]
NOTE: This is the Excel XP tagset (Compatible with SAS 9.1.3 and above, v1.94, 09/09/12). Add
options(doc='help') to the ods statement for more information.
[/pre]

The only other thing I can think of is that you are running an older version of TAGSETS.EXCELXP.

Otherwise, for help with your issue, you might want to work with Tech Support. It does not look to me like you should be having any issues with your code.

cynthia

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
  • 5 replies
  • 3011 views
  • 0 likes
  • 2 in conversation