The SAS Output Delivery System and reporting techniques

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

Accepted Solution Solved
Reply
Frequent Contributor
Frequent Contributor
Posts: 139
Accepted Solution

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

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


Accepted Solutions
Solution
‎12-26-2013 12:39 PM
SAS Super FREQ
Posts: 8,814

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

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;


use_microsoft_format.png

View solution in original post


All Replies
Solution
‎12-26-2013 12:39 PM
SAS Super FREQ
Posts: 8,814

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

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;


use_microsoft_format.png
Frequent Contributor
Frequent Contributor
Posts: 139

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

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 413 views
  • 0 likes
  • 2 in conversation