The SAS Output Delivery System and reporting techniques

tagsets.ExcelXP multiple formats in one column

Accepted Solution Solved
Reply
Contributor
Posts: 35
Accepted Solution

tagsets.ExcelXP multiple formats in one column

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

multiple formats.png


Accepted Solutions
Solution
‎03-28-2014 08:39 PM
SAS Super FREQ
Posts: 8,820

Re: tagsets.ExcelXP multiple formats in one column


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;

View solution in original post


All Replies
Super Contributor
Posts: 543

Re: tagsets.ExcelXP multiple formats in one column

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

Solution
‎03-28-2014 08:39 PM
SAS Super FREQ
Posts: 8,820

Re: tagsets.ExcelXP multiple formats in one column


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;

Contributor
Posts: 35

Re: tagsets.ExcelXP multiple formats in one column

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

🔒 This topic is solved and locked.

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

Discussion stats
  • 3 replies
  • 489 views
  • 0 likes
  • 3 in conversation