topic WANT TO USE TWO TAGATTR in SAME Column in ODS and Base Reporting

WANT TO USE TWO TAGATTR in SAME Column

patel1987 — Fri, 30 Dec 2011 19:35:49 GMT

Hello,

I create multiple sheet in Excel from SAS. And I am using ODS tagset Excel.XP for creating report. But I want to put thousand seperator in perticular place. I already put this suboption in option list in tagset. But in excel it is not showing comma in numbers.

For example,

I have one column which is character. And i am using PROC REPORT. But I have two value one Total count(Numeric) and Percent(%). I want to put comma in Numbers and put format=percen7. for PERCENT.

I use the following code,

PROC REPORT data =X;

in option of tagset.

Thanks.

Column ABC CDE;

Define ABC/ Desplay left;

DEFINE CDE/ DISPLAY left style={tagattr="format:###,###,###"};

run;

But this code change percent value. So I need to put different format in single column. I also put

CONVERT_PERCENTAGES=

'yes'

WANT TO USE TWO TAGATTR in SAME Column

Tom — Fri, 30 Dec 2011 19:47:51 GMT

Are you saying that for some observations the value of CDE is a count and for others it is a percent?

Did you trying using a COMPUTE block to assign the style?

WANT TO USE TWO TAGATTR in SAME Column

patel1987 — Fri, 30 Dec 2011 21:07:16 GMT

Hi,

Thanks Tom.

Yes it is correct and I have those values not constant. It is like, first OBS count then second OBS percent then third count and fourth percent like this. So, I think I can not put COMPUTE block..

Thanks.

Re: WANT TO USE TWO TAGATTR in SAME Column

Cynthia_sas — Fri, 30 Dec 2011 22:36:47 GMT

Hi:

I don't understand what you mean when you say you "want to put thousand separator in perticular place" -- if the thousand separator doesn't go between the thousands, then where does it go?? Do you mean you want periods as the thousands separator, instead of commas??? Or, for example, if I have this number:

123456789 then I would normally put the commas as thousands separators here (between the 3 and the 4 and between the 6 and the 7):

123,456,789

In what particular place do you want the commas to be placed???

Also, do I understand correctly that column ABC is a character column? And the values for ABC are what???

And that column CDE is a numeric column??? I ask because you can only use SAS formats with numeric columns, so if CDE is character, then you need to do some other processing in order to use a numeric format with a character column. Also, you want both ABC and CDE to be left-justified????

Generally, the LEFT option is ignored by some ODS destinations and I think that TAGSETS.EXCELXP is one of them. I use STYLE= overrides for justification. You can also use the CALL DEFINE statement such as that shown below to produce output, as shown in the attached screen shot. (created in SAS 9.3 and opened with Excel 2010 using some fake data, where the values of ABC were either Count or Percent). I show the CALL DEFINE with the FORMAT argument, because that will work for other destinations like HTML, RTF and PDF. Then I use CALL DEFINE with the STYLE argument for different values for TAGATTR.

cynthia

** code snippet;

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

options(doc ='Help');

proc report data=testdata nowd;

title 'Using CALL DEFINE';

Column grp abc cde;

define grp / order;

Define abc/ display

style(column)={just=l};

DEFINE cde/ display

style(column)={just=l};

compute cde;

if abc = 'Count' then do;

call define(_col_,'format','Comma9.');

call define(_col_,'style','style={tagattr="###,###,###"}');

end;

else if abc = 'Percent' then do;

call define(_col_,'format','Percent9.2');

call define(_col_,'style','style={tagattr="###%"}');

end;

endcomp;

run;

ods _all_ close;

WANT TO USE TWO TAGATTR in SAME Column

patel1987 — Sat, 31 Dec 2011 17:26:53 GMT

Cynthia,

Thanks.

I want to put format in perticular OBS. Like you understad excactly what I want to say. But thing is I have both character columns. But like I have different OBS in ABC column.

ABC CDE

Russia 20,000

Russia_PCT 4%

US 45,000

US_PCT 9%

Total 500,000

Like this. And I want to put thousand seperator in Numbers and Percent in relative record. And I am using SAS EG 4 and MS Excel 2003....

Or can I use this way?

** code snippet;

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

options(doc ='Help');

proc report data=testdata nowd;

title 'Using CALL DEFINE';

Column grp abc cde;

define grp / order;

Define abc/ display

style(column)={just=l};

DEFINE cde/ display

style(column)={just=l};

compute cde;

if abc = 'Russia' or 'US' or 'Total' then do;

call define(_col_,'format','Comma9.');

call define(_col_,'style','style={tagattr="###,###,###"}');

end;

else if abc = 'Russia_PCT' or 'US_PCT' then do;

call define(_col_,'format','Percent9.2');

call define(_col_,'style','style={tagattr="###%"}');

end;

endcomp;

run;

ods _all_ close;

Thanks.

WANT TO USE TWO TAGATTR in SAME Column

Tom — Sat, 31 Dec 2011 17:31:53 GMT

Looks good, other than the syntax of your IF condition.

Try abc in ('Russia','US','Total')

Re: WANT TO USE TWO TAGATTR in SAME Column

Cynthia_sas — Sat, 31 Dec 2011 23:35:15 GMT

Hi:

Tom is correct about the syntax of your IF statement. That would have to change.

Also, the CALL DEFINE statement for FORMAT assumes that the variable CDE is numeric. If CDE is character, then you would need to turn the character string into a number (using the INPUT function) before applying a numeric format.

cynthia

Added:

And, as much as I like PROC REPORT, this is one of those instances where PROC TABULATE might be a better choice, because you can get multiple statistics in the row dimension and then apply different formats to each statistic. This also means that you would not have to pre-summarize your data, but could, for example, get the N and the PCTN directly from the detail data. For example, SASHELP.CLASS has 19 total observations, 10 boys and 9 girls. It is possible to use TABULATE directly on SASHELP.CLASS to get the report you want. The PROC FORMAT step creates a PICTURE format, which you need because when TABULATE calculates PCTN, it does an automatic multiply by 100, and that means you can't use a regular SAS format because the PERCENT format also does a multiply by 100.

This code produced the attached screen shot (results opened in Excel 2010).

proc format;

picture pct low-high='009.99%';

run;

ods tagsets.excelxp file='c:\temp\use_tabulate.xml'

style=sasweb;

proc tabulate data=sashelp.class;

class sex;

table sex=' '*(n*f=comma9. pctn*f=pct.)

all*(n*f=comma9. pctn*f=pct.),

all='Statistics'

/row=float

box=sex;

label sex='Gender';

run;

ods _all_ close;

Edited message to add TABULATE example