BookmarkSubscribeRSS Feed
gyambqt
Obsidian | Level 7

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

9 REPLIES 9
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Obsidian | Level 7

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
Barite | Level 11

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
Obsidian | Level 7

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
Barite | Level 11

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
Obsidian | Level 7

Hi K C Wong,

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

gyambqt
Obsidian | Level 7

Hi Rob,

I have revised my question. please take a look.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Super User

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.

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
  • 1837 views
  • 0 likes
  • 4 in conversation