BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
dataMart87
Quartz | Level 8

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

multiple formats.png

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ


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

3 REPLIES 3
AncaTilea
Pyrite | Level 9

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

Cynthia_sas
SAS Super FREQ


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
Quartz | Level 8

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

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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