topic PROBLEM with COLUMN WIDTH in Developers

PROBLEM with COLUMN WIDTH

patel1987 — Sat, 24 Dec 2011 23:26:55 GMT

Hi,

I have a problem with column width in table. I am using ODS TAGSET.XP but when I give an output in Excel it creats n ERROR. I have column which is character and having lentgh $255.

I am using the following code

ODS tagset EXCEL.XP

options

( EMBENDED_TITLE='yes'

ABSLOUTE_COLUMN_WIDTH='28,255"

sheet_name='ABC'

;

Data x;

setx;

run;

PROC REPORT data =x;

Coulmn y z;

Define y / Display width=28 flow;

Define z / Display width=255 flow;

run;

It is simple process. I don't know why this create error when it converts XML to Excel. Please give some solution I appreciate.

Thanks.

PROBLEM with COLUMN WIDTH

art297 — Sat, 24 Dec 2011 23:48:43 GMT

Could the problem simply be unmatched quotes: '28,255"

I'm not sure if single or double quotes are needed, but I would think that the mixture would cause a problem.

PROBLEM with COLUMN WIDTH

patel1987 — Sun, 25 Dec 2011 01:48:20 GMT

Hi art,

Thanks but I am sorry. I made mistake here it was actally in ' ' single quoate. Also I forgot to put Default_column_wdth there. But still getting error.

I also put ODS TAGSET EXCEL. XP file=" " path=" " style= sasweb (doc=Help)

then options(

);

SAS CODE

ODS TAGSET CLOSING;

Re: PROBLEM with COLUMN WIDTH

Cynthia_sas — Sun, 25 Dec 2011 23:36:37 GMT

Hi:

Is this really a stored process question? I wonder because when you use ODS TAGSETS.EXCELXP, you are generally NOT using stored processes, unless your stored process is being run in the Information Delivery Portal. Also, you do not show the use of %STPBEGIN or %STPEND, which is usually part of a stored process, possibly with an override to &_ODSDEST.

Since you do not show this kind of invocation, perhaps you meant to post this in the ODS and Base Reporting forum???

Also,

ODS TAGSET CLOSING;

is not valid code. Nor is ODS TAGSET EXCEL.XP a valid invocation.

Usually, a valid invocation will be something like this:

ODS TAGSETS.EXCELXP file='c:\temp\filename.xml' style=sasweb

options(doc='Help' absolute_column_width="28,25" embedded_titles='yes');

proc print data=sashelp.class;

run;

ODS TAGSETS.EXCELXP CLOSE;

I would recommend you verify that you are using the correct syntax to invoke your code.

cynthia

PROBLEM with COLUMN WIDTH

patel1987 — Mon, 26 Dec 2011 00:25:40 GMT

Thanks Cynthia,

sorry for not explain very well to. I used follwoing code :

ODS TAGSETS.EXCELXP file='c:\temp\filename.xml' style=sasweb

options(doc='Help'

absolute_column_width="28,25"

DEFAULT_COLUMN_WIDTH='20'

embedded_titles='yes');

PROC SQL noprint;

SQL PASS THROUGH FACILITY;

CREATE TABLE ABCD as

SELECT COLUMN1, COLUMN2

FROM XYZ ;

quit;

PROC REPORT DATA=ABCD;

DEFINE COLUMN1/DISPLAY left;

DEFINE COLUMN2/ DISPLAY left;

run;

ODS TAGSETS.EXCELXP CLOSE;

NOW IN THIS CODE I HAVE TWO COLUMN WHICH IS COLUMN1 AND COLUMN2 WHICH BOTH CHARACTER. I PULLED THIS DATA FROM DATABASE. COLUMN2 HAS LENGTH $255. SO WHEN I GIVE AN OUTPUT IN EXCEL IT CREATE AN ERROR. IN SAS, IT CREATES DATASET WITH CORRECT DATA BUT ONLY WHEN GIVE AN OUTPUT FILE IS NOT OPEN. By the way I am using SAS EG 4.

I also try the following table:

ODS TAGSETS.EXCELXP file='c:\temp\filename.xml' style=sasweb

options(doc='Help'

absolute_column_width="28,255"

DEFAULT_COLUMN_WIDTH='20'

embedded_titles='yes');

PROC REPORT DATA=ABCD;

DEFINE COLUMN1/DISPLAY left;

DEFINE COLUMN2/ DISPLAY width=255 flow left;

run;

ODS TAGSETS.EXCELXP CLOSE;

PROBLEM with COLUMN WIDTH

Cynthia_sas — Mon, 26 Dec 2011 03:49:59 GMT

Hi:

I don't really understand what you want. It seems like you want to make the first column relatively small and the second column larger. By your use of the FLOW option, I wonder whether you want the long text string to wrap inside the cell.

It seems you are using options like WIDTH= and FLOW to try to "fix" your issues; however, these options will be ignored by ODS destinations like HTML, PDF, RTF and TAGSETS.EXCELXP -- options like WIDTH, FLOW, SKIP, HEADLINE, HEADSKIP, etc are LISTING only options. Since you are using TAGSETS.EXCELXP, there is no point in using these options.

I am guessing that you are getting wide columns, but that the height of the cells do not allow you to see the whole text string. If you have large text strings, frequently you need to change the row height suboptions, as well as the column width suboption. The doc='Help' suboption should show you the row height related suboptions for TAGSETS.EXCELXP in the SAS log.

Again, it does not seem like your question is related to stored processes or using the BI Platform. If you continue to have issues, you might want to work with Tech Support on this question.

cynthia

PROBLEM with COLUMN WIDTH

patel1987 — Mon, 26 Dec 2011 04:27:26 GMT

Thanks Cynthia,

Yes you are right. I am just trying to solve an issue. So, I put WIDTH= and FLOW options.

I also try the following options:

ods tagsets.ExcelXp options(embedded_titles='yes'

EMBED_TITLES_ONCE='yes'

CONVERT_PERCENTAGES='yes

sheet_interval='none'

orientation='landscape'

pagebreaks='yes'

TITLE_FOOTNOTE_WIDTH='5'

doc='help'

zoom='60'

ABSOLUTE_COLUMN_WIDTH='28,255'

DEFAULT_COLUMN_WIDTH='25'

wraptext='yes'

thousands_separator=','

autofit_height='yes'

);

SAS code;

ods tagsets.ExcelXp close;

Still getting same error. If, I drop wider Column from dataset then its not create an error. But when keep it in dataset erro comes up. Now what I am suppose to try to fix this issue?

Thanks

PROBLEM with COLUMN WIDTH

Cynthia_sas — Tue, 27 Dec 2011 16:48:57 GMT

This question was reposted and answered in the ODS forum: http://communities.sas.com/message/112209

cynthia