ExcelXP Tagset: Two questions regarding formatting of a number as a char var and autofill options

[LB]

Hello all-

Hope everybody has a great holiday so far-

I have two questions regarding ExcelXP Tagsets -I am using SAS 9.2.3

1) I am attempting to use the autofilter function- I want to use only specific columns-1,9,10 for example-

When I attempt to code it as options(autofilter='1,9' ) it makes autofiters available for all the columns, for which I don't want-

Is there a way to specifically pick only the columns desired.

2) I have a character variable that is numeric in nature (i.e. 11000xxxxx8)-I have set the length for 12 and the format for $12.

When I output to the ExcelXP tagset it renders it as

1.10003E+11

Any formatting solutions would be great!

Many Thanks.

Lawrence

[Cynthia_sas · Accepted solution]

Hi, I am not sure about the autofilter option, that would be a question for Tech Support. The internal doc says that the valid values for autofilter suboption are NONE, ALL or provide a range. It does not say that you can autofilter on specific columns.

  But, about the big numeric variable, Excel basically ignores your SAS format -- whether the variable is character or numeric. One option is to tell Excel the string is a character string and then you will get the hated green triangle in the cell in Excel (number as character) or you can provide a Microsoft format big enough for the number to be displayed. So, with TAGSETS.EXCELXP, you have to use a MICROSOFT numeric format via the TAGATTR style override. This paper explains a lot more, (http://support.sas.com/resources/papers/proceedings11/266-2011.pdf) but here's a basic example.

Cynthia

data bignum;

  set sashelp.class;

  newage1 = 1234560800+age;

  ** make new var REALLY big;

  if _n_ = 3 then newage1 = newage1*100;

  newage2 = newage1;

run;

ods tagsets.excelxp file='c:\temp\bignum.xml' style=sasweb;

** NEWAGE will use Excel defaults but NEWAGE2 will use Microsoft format;

proc print data=bignum(obs=3);

  title 'See diff in Excel with NEWAGE1 vs NEWAGE2';

  var name age sex height weight newage1;

  var newage2 / style(column)={tagattr='###,###,###,###'};

run;

ods _all_ close;

[LB]

Cynthia-

The assistance is always most helpful-Thank you.

I can call tech support or just switch the order of the columns to group them accordingly!

Thanks on the formatting issue. That helps too.

Lawrence