BookmarkSubscribeRSS Feed
sasmom
Fluorite | Level 6

I want to create an excel file with two tabs, sheet1 and sheet2. I want table1 and table2 data on a tab and table3 data in the another tab. Below is my code. I tried all options for sheet_interval. The 'sheet_interval = none' option create one tab with three tables. All the other options (page, bygroup, table and proc) create three tabs. The table1 and table2 data come on two different tabs.

 

Thanks for your help!

*******************************************************************************************************************************

ods listing close; 

ods tagsets.ExcelXP style=journal file="excel_file.xls"

options(sheet_name=sheet1' autofilter='none' orientation='landscape'

                      sheet_interval='none'

                     SKIP_SPACE= '5');

     proc print data=table1 noobs

     style(header) = { font_style=italic font_weight = bold font_face = "calibri" font_size = 10pt just = c}

     style(data) = {font_face = "Calibri" font_size = 10pt background=$nfmt.foreground=white};

     run;

      proc print data=table2 noobs

     style(header) = { font_style=italic font_weight = bold font_face = "calibri" font_size = 10pt just = c}

     style(data) = {font_face = "Calibri" font_size = 10pt};

     run;

 

ods tagsets.ExcelXP options(sheet_name='sheet2' autofilter='all' orientation='landscape'

                                     frozen_headers='Yes');

     proc print data=table3 noobs

     style(header) = { font_style=italic font_weight = bold font_face = "calibri" font_size = 10pt just = c}

     style(data) = {font_face = "Calibri" font_size = 10pt};

    

ods tagsets.ExcelXP close;

 

**********************************************************************************************************

 

3 REPLIES 3
RW9
Diamond | Level 26 RW9
Diamond | Level 26

This should work:

ods tagsets.excelxp file="s:\temp\rob\temp.xml" options(sheet_interval="none" sheet_name="First");
proc report data=sashelp.class nowd;
  columns _all_;
run;
proc report data=sashelp.class nowd;
  columns _all_;
run;
ods tagsets.excelxp options(sheet_interval="table" sheet_name="Next");
proc report data=sashelp.class nowd;
  columns _all_;
run;
ods tagsets.excelxp close;
sasmom
Fluorite | Level 6

Thanks a lot! That worked.

Reeza
Super User

Using the following line will tell Excel to start a new sheet.  Anything that follows this should be on the same tab. If you want three sheets, you'd have two of these lines - one before the code for each tab, and the initial statement of options. 

 

ods tagsets.excelxp options(sheet_interval='none');

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
  • 3 replies
  • 972 views
  • 0 likes
  • 3 in conversation