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
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;
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;
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.