BookmarkSubscribeRSS Feed
Sirko606
Fluorite | Level 6

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.

 

 

9 REPLIES 9
Reeza
Super User

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. 

Sirko606
Fluorite | Level 6

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.

 

 

 

Reeza
Super User

I may be missing something but your code is 

 

sheet_interval='byfulknavn' not BYGROUP?

 

 

Sirko606
Fluorite | Level 6

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.

 

Reeza
Super User

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. 

Sirko606
Fluorite | Level 6
i am using sas EG 7.11 HF3 with sas 9.4.
we recently upgraded local sas to 9.4 from 9.3.
Reeza
Super User

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

Cynthia_sas
SAS Super FREQ

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

 

Sirko606
Fluorite | Level 6

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.

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