Is there a way to have multiple formats in one column in an output from ODS TAGSETS.EXCELXP?
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;
if this is how your data looks like in SAS, then I don't see why not.
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;
I tried the inline formatting approach, but that did not work. This does. Thank you.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.