tagsets.ExcelXP multiple formats in one column

[dataMart87]

Is there a way to have multiple formats in one column in an output from ODS TAGSETS.EXCELXP?

[AncaTilea]

if this is how your data looks like in SAS, then I don't see why not.

[Cynthia_sas · Accepted solution]

Hi:

  Yes, you can do this with PROC TABULATE and/or PROC REPORT. Since you didn't show your input data, I made some fake data for PROC REPORT and used SASHELP.CARS for TABULATE.

Cynthia

data new;

infile datalines dlm=',';

input ordvar var_a $ var_b;

return;

datalines;

1.1,numadmit, 30

1.2,avgstay, 6.78

1.3,planamt,2000

2.1,numadmit, 50

2.2,avgstay, 7.337

2.3,planamt,55555.1

;

run;

title;

ods _all_ close;

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

proc report data=new nowd;

column ordvar var_a var_b;

define ordvar / order;

define var_a / display 'Column A';

define var_b / display 'Column B';

compute var_b;

  if var_a = 'numadmit' then do;

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

  end;

  else if var_a = 'avgstay' then do;

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

  end;

  else if var_a = 'planamt' then do;

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

  end;

endcomp;

run;

** show diff formats with TABULATE and more columns;

** apply format to different variables;

proc tabulate data=sashelp.cars;

where make in ('Acura' 'Audi' 'BMW' );

var mpg_city mpg_highway cylinders / style={width=1.5in};

class make;

table mpg_city*{style={tagattr="000,000"}}

      mpg_highway*{style={tagattr="###,##0.00"}}

      cylinders*{style={tagattr="$###,##0"}},

      make / row=float;

keylabel sum=' ';

run;

ods tagsets.excelxp close;

[dataMart87]

I tried the inline formatting approach, but that did not work.  This does.  Thank you.