ods tagset excelxp display issue

[gyambqt]

Hello expert,

The following program will produce a xml output which can be opened by excel.

When you open the xml output in excel, it has two tabs, "a" and "b".

Within "b" tab, it has three column, "c", "d", "e"

My question is how do I conceal the content of column "e" so It doesn't  go beyond the boundary of column e.(I have tried wraptext="No" but it didn't work, it seem the previous wraptext="yes" has override the subsequent one)

or how do I expand the row height (have tried row_height option but still not work).

data a;
length a $500 b$500;
a="sdakfasdfgsdfgsdfgdfgsdfgsdfsdfgdfgafgasdfgsdfasd.jadfklhlagjsdklfnasdklnklfnklgnklsdngalsdbnglasdgasod";
b="asdskdfahklasdjklfjasdlsdfhsdgfjsadjkfhasdjklfhjkasdhfjkhasdjkfhjkasdhfjksdkfjasjkfhsdkajfsdafhasdjkfhasdgfasdgfhasdfgasdgfgsdhfjgasdjkf";
run;

data b;
length c $500 d $500 e $500;
c="asdskdfahklasdjklfjasdlsdfhsdgfjsadjkfhasdjklfhjkasdhfjkhasdjkfhjkasdhfjksdkfjasjkfhsdkajfsdafhasdjkfhasdgfasdgfhasdfgasdgfgsdhfjgasdjkf";
d="asdskdfahklasdjklfjasdlsdfhsdgfjsadjkfhasdjklfhjkasdhfjkhasdjkfhjkasdhfjksdkfjasjkfhsdkajfsdafhasdjkfhasdgfasdgfhasdfgasdgfgsdhfjgasdjkf";
e="asdskdfahklasdjklfjasdlsdfhsdgfjsadjkfhasdjklfhjkasdhfjkhasdjkfhjkasdhfjksdkfjasjkfhsdkajfsdafhasdjkfhasdgfasdgfhasdfgasdgfgsdhfjgasdjkf";
run;

ODS LISTING CLOSE;

ODS TAGSETS.EXCELXP FILE="c:\a.xls"                              

OPTIONS(SHEET_NAME='a'
ABSOLUTE_COLUMN_WIDTH='18'
AUTOFIT_HEIGHT="YES"
WRAPTEXT="NO")
style=Printer;

proc report data=a nowd;
run;

ODS TAGSETS.EXCELXP OPTIONS(SHEET_NAME='b'
ABSOLUTE_COLUMN_WIDTH='18'
AUTOFIT_HEIGHT="YES"
WRAPTEXT="YES")
style=Printer;

proc report data=b nowd;
run;

ods tagsets.ExcelXP close;

Thanks

[RW9]

You cna use the row_heights option to fix the height of cells, and hence what is displayed.  Use this in combination with style(column)=[cellwidth=] to shrink cell:

ODS LISTING CLOSE;

ODS TAGSETS.EXCELXP FILE="s:\temp\rob\a.xls"                              

OPTIONS(SHEET_NAME='a'

ABSOLUTE_COLUMN_WIDTH='5'

row_heights="1")

style=Printer;

proc report data=a nowd;

  define a / style(column)=[cellwidth=2cm];

run;

ods tagsets.ExcelXP close;

[gyambqt]

Hi Rob,

I have tried to run your code. But it didn't work.  the width of the column has been reduced but the content for that column has exceeded the boundary if it is the first column on the right side.

I am wondering how do I make it wraptext?

I have specified wraptext="Yes" but if the wraptext setting for the previous ods tagset output (same excelsheet just multiple tabs) is wraptext="yes", it seems it will override the setting of wraptext="No" for the subsequent ods tagset output.

[Miracle]

Hi Gyambqt,

This thread https://communities.sas.com/message/43485 will be able to ask your question.

However,

ODS LISTING CLOSE;

ODS TAGSETS.EXCELXP FILE="\\Desktop\a.xls" ;                            

ODS TAGSETS.EXCELXP OPTIONS(SHEET_NAME='a'

ABSOLUTE_COLUMN_WIDTH='5'

AUTOFIT_HEIGHT="YES"

WRAPTEXT="YES"

)

style=Printer;

proc print data=a;

run;

ODS TAGSETS.EXCELXP OPTIONS(SHEET_NAME='b'

ABSOLUTE_COLUMN_WIDTH='NONE'

AUTOFIT_HEIGHT="NONE"

WRAPTEXT="NO"

);

proc print data=a;

run;

ods tagsets.ExcelXP close;

Thank you.

[gyambqt]

Hi K C Wong,

I have revised my question. Please take a look. I think you did not really understand my question. Please run the code so you will be able to see.

Thanks

[Miracle]

Hi Gyambqt,

If I understand you correctly, you wanna apply different worksheet options to different worksheets and to do this you need to first create the Excel file and that is first you specify this statement ODS TAGSETS.EXCELXP FILE="\\Desktop\a.xls" ;

only then you specify the next statement for the worksheet options for that particular worksheet ODS TAGSETS.EXCELXP OPTIONS(SHEET_NAME='a' ...);

If next worksheet with different options then specify the new statement ODS TAGSETS.EXCELXP OPTIONS(SHEET_NAME='b' ...);

Thanks.

[gyambqt]

Hi K C Wong,

Have you tried my program and do you have any solution for my problem?

[gyambqt]

Hi Rob,

I have revised my question. please take a look.

[RW9]

Hi,

Well, you are writing out a file that needs to be interpreted by Excel, so there are limitations.  I don't have time at the moment to really follow up on the question.  My suggestion would be to do your formatting in Excel if its really this fixed and non-standard.  I.e. have an Emtpy Excel file in which you create a small macro to do the formatting.  Then open the two files and run the macro over your output.  You can record actions in Excel to get an idea of the needed VBA code. 

The other option, although I haven't used it so not sure how limited it would be, is to look at the new XLSX libname engine in 9.4.  Apparently this can write data out to the XLSX format, so in theory you could create a file with all your formatting etc already present, and then a sheet for raw data, write your raw data out to this sheet, and then have it auto populate in the fomatted tab.  If you have 9.4, maybe investigate this first.  Here is some help:

http://www.lexjansen.com/nesug/nesug13/89_Final_Paper.pdf

[Kurt_Bremser]

I guess that is not solvable from SAS, as it is not solvable in Excel itself. If you don't wrap the text in the cell, overlong values in cell n will always display over the cells n+1,n+2,...., unless you put at least a blank into cell n+1.