topic Re: Stubborn Text - even with tagattr in ODS and Base Reporting

Stubborn Text - even with tagattr

steve_citi — Thu, 10 Dec 2009 16:09:19 GMT

Regardless of the tagattr used, variable _200911 continues to come up as text in excel. It's attributes are the same as workbal and cur_bal, which come up numeric.

ods tagsets.excelxp file="/usr04/spm/banking/tcc/rohan/output/forbearance_test_&stopmon..xls" style=styles.vminstyle
options(sheet_name="Payments" frozen_headers="yes");
PROC PRINT data=forbear6_trans_rpt LABEL split='*';
format workbal cur_bal _200911 dollar11.2;
label work_date="Forbearance*Date";
label addr_1 ="Address 1";
label addr_2 ="Address 2";
label st ="State";
label obg_no ="Obligor*Number";
label obl_no ="Obligation*Number";
label proddesc ="Product*Name";
label workbal ="Balance*at*Workout";
label cur_bal ="Current*Balance";
id work_date ;
var name ;
var addr_1 ;
var addr_2 ;
var city ;
var st ;
var zip ;
var obg_no ;
var obl_no ;
var proddesc ;
var workbal ;
var cur_bal ;
var _200911 /style(data)={tagattr="format:Currency"};
sum cur_bal workbal;
run;

ods tagsets.excelxp close;

Re: Stubborn Text - even with tagattr

Cynthia_sas — Thu, 10 Dec 2009 17:56:56 GMT

Hi:
That's odd. When I try this code, I -do- see dollar signs for both ACTUAL and PREDICT. I have to admit that I like the way Excel treats ACTUAL better than the way it treats PREDICT. But this code works for me; and, ACTUAL and PREDICT are both numeric columns in Excel.

If you run this code and do not see dollar signs for both ACTUAL and PREDICT, then you should check that you are running the most current version of the tagset template. I ran this code in SAS 9.2 using ExcelXP version:
NOTE: This is the Excel XP tagset (SAS 9.1.3, v1.86, 04/15/08).

If you are using this version of the tagset and your code does not produce dollar signs and numeric values in Excel, then your best resource is to open a track with Tech Support on this issue.

cynthia
[pre]
ods tagsets.excelxp file="c:\temp\testcurr.xls" style=sasweb
options(sheet_name="Payments" frozen_headers="yes");

PROC PRINT data=sashelp.prdsale(obs=3) LABEL split='*';
format actual predict dollar11.2;
id month ;
var actual /style(data)={tagattr="format:$#,#00.00"};
var predict /style(data)={tagattr="format:Currency"};
sum actual predict;
run;

ods tagsets.excelxp close;
[/pre]

Re: Stubborn Text - even with tagattr

steve_citi — Thu, 10 Dec 2009 18:16:02 GMT

Cynthia,

I am using version 1.86 and dollar signs DO come out, that was never a problem. However, the field in Excel is text.

Re: Stubborn Text - even with tagattr

Cynthia_sas — Thu, 10 Dec 2009 18:33:47 GMT

That's odd, my fields are numeric when I use either of my TAGATTR values. I am forced to conclude that it's
1) something different between your data and SASHELP.PRDSALE and your numeric values ???
2) something in your style template
3) something in Excel

I seriously doubt it's #1. That leaves #2 or #3, which really require the expertise of Tech Support.

cynthia

Re: Stubborn Text - even with tagattr

steve_citi — Thu, 10 Dec 2009 18:36:31 GMT

One possibility is that this field (_200911) was created with a transpose.

However, its attributes are numeric. It is the only field which is giving me any difficulty.

Re: Stubborn Text - even with tagattr

Cynthia_sas — Thu, 10 Dec 2009 19:05:21 GMT

Hi:
I doubt it was PROC TRANSPOSE -- if TRANSPOSE created a numeric variable _200911 (which it -must- have if you can successfully use the DOLLAR11.2 format), then I doubt the creation method is a factor. You could run an experiment to explicitly create a numeric copy of _200911:
[pre]
data new;
set ...;
length new_v200911 8;
new_v200911 = _200911;
run;
[/pre]

and then run your EXCELXP code using both new_v200911 and _200911 and see if new_v200911 comes across as numeric or text in Excel. But even if the new variable did come across as numeric I'm not sure what that would mean.

As I said, since you're using a dollar format with _200911, it would have to be numeric. Interestingly, when I run the program below, even the character variable _2009xx comes across as numeric in Excel, due to TAGATTR, even though it is CHARACTER in SAS. I guess it just shows that Excel likes and respects Excel formats more than it respects SAS formats.

It's a puzzle. My recommendation is to work with Tech Support.

cynthia
[pre]
data test;
length _2009xx $11;
set sashelp.prdsale;
_200911 = actual;
_2009xx = put(actual, dollar11.2);
run;

ods tagsets.excelxp file="c:\temp\testcurr2.xls" style=sasweb
options(sheet_name="Payments" frozen_headers="yes");

PROC PRINT data=test(obs=3) LABEL split='*';
format actual predict _200911 dollar11.2;
id month ;
var actual /style(data)={tagattr="format:$#,#00.00"};
var predict /style(data)={tagattr="format:Currency"};
var _200911 /style(data)={tagattr="format:Currency"};
var _2009xx / style(data)={tagattr="format:Currency"};
sum actual predict _200911 ;
run;

ods tagsets.excelxp close;
[/pre]

Re: Stubborn Text - even with tagattr

steve_citi — Thu, 10 Dec 2009 20:19:09 GMT

Cynthia,

Creating a new variable with a length of 8 seemed to work.

Re: Stubborn Text - even with tagattr

Cynthia_sas — Thu, 10 Dec 2009 20:29:51 GMT

Hi:
I'm glad. Puzzled, but glad it worked. (the only thing that the code would have done was take any stray or odd missing values in the variable _200911 and explicitly set them to SAS missing or . in NEW_V200911 variable.);

cynthia