Hello everyone,
I have a sample code as below. I try to get my reports being a ODS Tagset.ExcelXP. However, if I get some reports by default, their widths's seems too long. So I found a weight option to prevent this situation. This time, the reports height's seems too large. How can I prevent this situation. My desired output as below.
Can somebody help me, please?
Data AppendixRoc;
Length Range_ $ 32 Explanation $ 32;
Infile Datalines Missover Delimiter="|";
Input Range_ Explanation;
Datalines;
ROC|Ayirim Yok
ROC|Zayif Ayirim
ROC|Kabul Edilebilir Ayirim
ROC|Mukemmel Ayirim
ROC|Olaganustu Ayirim
;
Run;
Proc Format;
Value $color "Ayirim Yok"="Red"
"Zayif Ayirim"="Orange"
"Kabul Edilebilir Ayirim"="Yellow"
"Mukemmel Ayirim"="LightGreen"
"Olaganustu Ayirim"="CX00B050";
Run;
Ods Tagsets.ExcelXP Path="C:\Temp" file="CAP.xml"
style=htmlblue options(zoom="80" embedded_titles='yes' sheet_interval='none' sheet_name="CAP");
PROC Report Data=AppendixRoc;
Title "ROC icin Genel Kural";
Columns Range_ Explanation;
Define Range_ /"" Display Style(Column)=Header {width=1.041666667in};
Define Explanation /"" Display style=[BackGround=$color. width=1.510416667in] ;
Run;
Title;
Ods Tagsets.ExcelXP Close;
Width
Height
Desired
Hi:
I don't think you want to specify 0. Here's the output using 10 and 20:
cynthia
Have you tried the height options in tagsets?
https://support.sas.com/rnd/base/ods/odsmarkup/excelxp_help.html
This is an excellent tagset reference, find the issue, and then it links to papers with examples on that topic.
Hi:
While you might have reports that cause you to need sub-options for height and/or width controls in TAGSETS.EXCELXP suboptions, I don't think this is one of those times. I suspect your attempt to "overcontrol" the width with measurements like:
Define Range_ /"" Display Style(Column)=Header {width=1.041666667in};
Define Explanation /"" Display style=[BackGround=$color. width=1.510416667in]
might be causing your issues. When I tried your code with slightly more reasonable widths, I got your desired output without the need for other sub-options. So I suspect something in the over-specified width caused Excel to render the rows in an undesired basis.
Here's my output:
And here were the ONLY changes I made to the program:
Define Range_ /"" Display Style(Column)=Header {width=1.05in};
Define Explanation /"" Display style=[BackGround=$color. width=1.8in] ;
cynthia
Hello @Reeza and @Cynthia_sas,
Thank you very much for trying to help me 🙂
@Cynthia_sas, I think you are right, it can be related to "width"'s option fractions. Hovewer, the title still seems too large. what can be the reason for?
I checked @Reeza's web-site links and I used the Auto_Height="Yes" option but it doesn't work, the height(title) is still too large.
Ods Tagsets.ExcelXP Path="C:\Temp" file="CAP.xml"
style=htmlblue options(zoom="80" embedded_titles='yes' sheet_interval='none' sheet_name="CAP" AUTOFIT_HEIGHT="yes");
PROC Report Data=AppendixRoc;
Title "ROC icin Genel Kural";
Columns Range_ Explanation;
Define Range_ /"" Display Style(Column)=Header {width=1.05in};
Define Explanation /"" Display style=[BackGround=$color. width=1.8in] ;
Run;
Title;
Ods Tagsets.ExcelXP Close;
Thank you,
The Excelxp tagset (and the new ODS destination for Excel) creates titles by merging multiple cells in the row. Excel's Autoheight feature doesn't work on rows that contain merged cells. This is an Excel limitation.
You should be able to use the ROW_HEIGHTS keyword in the OPTIONS() option to specify a height for the title row.
Hello @Tim_SAS,
I don't exactly understand how can I use the Row_Heights option. I tried to use the following statements but it seems it didn't work. Maybe, I used the option incorrect. Can you help me to fix the title of report.
Ods Tagsets.ExcelXP Path="C:\Temp" file="CAP.xml"
style=htmlblue options(zoom="80" Row_Heights="0,0,0,0" Embedded_Titles='yes' Autofit_Height="yes" sheet_interval='none' sheet_name="CAP");
Thank you,
Hi:
I don't think you want to specify 0. Here's the output using 10 and 20:
cynthia
Thank you very much @Cynthia_sas, it worked.
But I don't really understand the logic behind of the values. Yes, it works when we write 0,0,0,10 but what do this values stand for? Do they stand for mm or inch or etc.?
I didn't understand from the following link -> https://support.sas.com/rnd/base/ods/odsmarkup/excelxp_help.html
Do you have an another links which explain more detailed?
Thank you
Hi:
if you run your code with doc='Help" in the suboption list, you will see everything about row_heights in the SAS log. All the suboptions are documented inside the template definition, so you need to use that suboption to see the full explanation.
I believe that the number you specify is in points. however, I believe the number is then further manipulated, based on other option values on the font and other items, like whether you are using the merge across option for the title, or width=. So if you need a further explanation, you will need to work with Tech Support, after you use doc='Help' in the code. (in the code snippet I posted, I show the use of doc='Help' in the suboption list -- it is the first item in the list)
cynthia
Yes, you are totally right. You told me before, sorry. I always forget the add doc="Help" suboption.
Thank you very much again 🙂
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.