The SAS Output Delivery System and reporting techniques

ODS excel and tagset.excelxp , proc report #BYVAL

Reply
Occasional Contributor
Posts: 7

ODS excel and tagset.excelxp , proc report #BYVAL

hi!

 

i have a problem with ods excel and proc report won't resolve #BYVAL when ods tagsets.excelxp does.

 

This is the initial code for creating the file, it is the same for excelxp and excel:

ods Excel/tagsets.excelxp file="...\F&f_nr._&ar_input.&termin_input..xls"
style=statistical options (suppress_bylines='yes');

 

This is the code for ods tagsets.excelxp:

ods tagsets.ExcelXP options(sheet_interval='byfullknavn' sheet_name='#BYVAL(fullknavn)');

proc report data=kommuner_&f_nr. headline headskip nowindows noalias;
column ("#BYVAL(fullknavn)" "&ar_input." '--' );
by fullknavn;

....

 

This is the code for ods excel:

ods excel options(sheet_interval='bygroup' sheet_name='#BYVAL(fullknavn)');

proc report data=kommuner_&f_nr. headline headskip nowindows noalias;
column ("#BYVAL(fullknavn)" "&ar_input." '--');
by fullknavn;

....

 

The question is with ods tagsets.excelxp the proc report column manage to resolve #BYVAL(fullknavn), but when i use ods excel the column in proc report won't resolve, and just prints #BYVAL(fullknavn).

both manage to resolve the #BYVAL for sheet_name.

 

 

Super User
Posts: 19,772

Re: ODS excel and tagset.excelxp , proc report #BYVAL

I don't think you have a valid sheet_interval setting. 

I think you might want BYGROUP? 

 

Not sure thats related to your issue though. 

Occasional Contributor
Posts: 7

Re: ODS excel and tagset.excelxp , proc report #BYVAL

hi

 

sheet_interval='bygroup' is valid for ods excel according to http://support.sas.com/documentation/cdl/en/odsug/67921/HTML/default/viewer.htm#p09n5pw9ol0897n1qe04...

 

to clarify, i want to use ods excel instead of ods tagsets.excelxp, the code for ods tagsets.excelxp is working like it should, resolving #BYVAL and splitting the pages in the excel-sheet as it should and naming the columns with #BYVAL.

 

 

 

Super User
Posts: 19,772

Re: ODS excel and tagset.excelxp , proc report #BYVAL

I may be missing something but your code is 

 

sheet_interval='byfulknavn' not BYGROUP?

 

 

Occasional Contributor
Posts: 7

Re: ODS excel and tagset.excelxp , proc report #BYVAL

yes, that's correct. but that code is working like it should, its not the ods tagsets.excelxp that is the problem.
but you're right, that sheet_interval might not be valid for tagsets.excelxp, and is only working because of sheet_name='#BYVAL(fullknavn)'

 

this code is the problem:

ods excel options(sheet_interval='bygroup' sheet_name='#BYVAL(fullknavn)');

proc report data=kommuner_&f_nr. headline headskip nowindows noalias;
column ("#BYVAL(fullknavn)" "&ar_input." '--');
by fullknavn;

 

proc report doesn't resolve #BYVAL for column, it just prints #BYVAL(fullknavn).

this is only a problem when i use ods excel, not ods tagsets.excelxp.

 

Super User
Posts: 19,772

Re: ODS excel and tagset.excelxp , proc report #BYVAL

That part I can't help with. But what version of SAS do you have? ODS Excel was experimental until recently. 

 

I'm sure someone from SAS will respond. 

Occasional Contributor
Posts: 7

Re: ODS excel and tagset.excelxp , proc report #BYVAL

i am using sas EG 7.11 HF3 with sas 9.4.
we recently upgraded local sas to 9.4 from 9.3.
Super User
Posts: 19,772

Re: ODS excel and tagset.excelxp , proc report #BYVAL

I think it's full prod in SAS 9.3M3 M2/M1 are still experimental. 

SAS Super FREQ
Posts: 8,864

Re: ODS excel and tagset.excelxp , proc report #BYVAL

Hi:

  I think you will need to check with Tech Support. When I run this test:

proc sort data=sashelp.class out=class;
by age;
where age in (12,13,14);
run;
    
ods _all_ close;
options nobyline;
ods pdf file='c:\temp\testby1.pdf';
ods rtf file='c:\temp\testby2.rtf';
ods html file='c:\temp\testby3.html';
ods excel file='c:\temp\testby_xl4.xlsx' options(sheet_interval='bygroup' sheet_name='#BYVAL(age)') style=htmlblue;
ods tagsets.ExcelXP file='c:\temp\testby_xp5.xml' options(sheet_interval='bygroup' sheet_name='#BYVAL(age)') style=htmlblue;
 
proc report data=class headline headskip nowindows noalias;
column ("#BYVAL(age)" age sex name height weight);
by age;
run;
ods _all_ close;

The ONLY destination that respects the #BYVAL in the COLUMN statement is TAGSETS.EXCELXP. I'm not sure why that works since HTML, PDF and RTF don't respect the #BYVAL in the COLUMN statement. I would expect ODS EXCEL to act more like PDF and RTF... so in the COLUMN statement output, you can see that ODS EXCEL acts exactly like RTF and PDF in how the COLUMN statement is treated. But the sheet_names are correctly named after the bygroup values. Here's what I see for ODS EXCEL and ODF RTF and ODS PDF output....

ods_excel_byval.png

 

 

But, bottom line, I am not sure it is possible to make ODS EXCEL work like ODS TAGSETS.EXCELXP so that the #BYVAL is respected in the COLUMN statement because I think that is a nice thing that got added to TAGSETS.EXCELXP that was never implemented for the other destinaitons. This is a question for Tech Support.

 

cynthia

 

Occasional Contributor
Posts: 7

Re: ODS excel and tagset.excelxp , proc report #BYVAL

[ Edited ]
Posted in reply to Cynthia_sas

thanks for the answers.

 

guess i just have to use excelxp tagsets, hope they will include #BYVAL in ods excel as well.

 

as a end to this, we have sent a feature request to SAS for this. hopefully they will include it sometime i the future.

Ask a Question
Discussion stats
  • 9 replies
  • 689 views
  • 0 likes
  • 3 in conversation