BookmarkSubscribeRSS Feed
patel1987
Calcite | Level 5

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.

7 REPLIES 7
art297
Opal | Level 21

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.


patel1987
Calcite | Level 5

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;

Cynthia_sas
SAS Super FREQ

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

patel1987
Calcite | Level 5

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;

Cynthia_sas
SAS Super FREQ

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

patel1987
Calcite | Level 5

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

Cynthia_sas
SAS Super FREQ

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

cynthia

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