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.
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.
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.
I may be missing something but your code is
sheet_interval='byfulknavn' not BYGROUP?
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.
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.
I think it's full prod in SAS 9.3M3 M2/M1 are still experimental.
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....
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
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.