topic Re: ODS Excel PROC PRINT style(data)={tagattr="format:###,###"} Not working in ODS and Base Reporting

ODS Excel PROC PRINT style(data)={tagattr="format:###,###"} Not working

pkm_edu — Tue, 29 Aug 2023 03:31:57 GMT

dm "log; clear; output; clear; odsresults; clear;";
Data Test;
infile datalines dlm = ',' ;
input group_cat :$12. racethx :$20. statistic :$3. value :8. ;
datalines;
final_sample, Hispanic,	            CV,	69.9
final_sample, Non-Hispanic Black,	CV,	82
final_sample, Asian,	            CV,	56.5
final_sample, Other,	            CV,	66.3
final_sample, Hispanic,	            Max,61436
final_sample, Non-Hispanic Black,	Max,	77065
final_sample, Asian,	            Max,	49129
final_sample, Other,	            Max,	101795
;
run;
data Test1;
 set Test;
 value_f = put(value, mixedvalue. -r);
run;
* get the desired output
proc print data=Test1 noobs blankline=4; 
var racethx statistic;
var value_f;
id group_cat;
run;
* do not get the desired output (as above) in Excel (Issue)
ods listing close;
ods excel file = "C:\Data\test.xlsx" 
   options (sheet_name = 'Sheet1');
proc print data=Test1 noobs blankline=4; 
var racethx statistic;
var value_f / style(data)={tagattr="format:###,###"}
              style(data)={just=r};
id group_cat;
run;
ods listing;

This part is not working: var value_f / style(data)={tagattr="format:###,###"}

style(data)={just=r};

Desired output in Excel

group_cat racethx statistic value_f

final_sample Hispanic CV 69.9
final_sample Non-Hispanic Black CV 82.0
final_sample Asian CV 56.5
final_sample Other CV 66.3

final_sample Hispanic Max 61,436
final_sample Non-Hispanic Black Max 77,065
final_sample Asian Max 49,129
final_sample Other Max 101,795

Re: ODS Excel PROC PRINT style(data)={tagattr="format:###,###"} Not working

ballardw — Tue, 29 Aug 2023 14:59:27 GMT

And just want is not appearing correctly? You show what you want but not what you actually get.

If the problem is value like 69 instead of 69.9 then tell me which part of {tagattr="format:###,###"} controls appearance of decimal points?

Re: ODS Excel PROC PRINT style(data)={tagattr="format:###,###"} Not working

pkm_edu — Tue, 29 Aug 2023 15:19:06 GMT

/* PROC FORMAT added (sorry forgot add the last time)

dm "log; clear; output; clear; odsresults; clear;";
proc format;
value mixedvalue
low-<100 = [4.1]
100-999 = [3.]
1000-high = [comma12];
run;
Data Test;
infile datalines dlm = ',' ;
input group_cat :$12. racethx :$20. statistic :$3. value :8. ;
datalines;
final_sample, Hispanic, CV, 69.9
final_sample, Non-Hispanic Black, CV, 82
final_sample, Asian, CV, 56.5
final_sample, Other, CV, 66.3
final_sample, Hispanic, Max,61436
final_sample, Non-Hispanic Black, Max, 77065
final_sample, Asian, Max, 49129
final_sample, Other, Max, 101795
;
run;
data Test1;
set Test;
value_f = put(value, mixedvalue. -r);
run;
proc print data=Test1 noobs blankline=4;
var racethx statistic;
var value_f;
id group_cat;
run;

ods listing close;
ods excel file = "C:\Data\test.xlsx"
options (sheet_name = 'Sheet1');
proc print data=Test1 noobs blankline=4;
var racethx statistic;
var value_f / style(data)={tagattr="format:###,###"}
style(data)={just=r};
id group_cat;
run;
ods listing;

Desired output in the Excel file is as follows:

group_cat racethx statistic value_f

final_sample Hispanic CV 69.9
final_sample Non-Hispanic Black CV 82.0
final_sample Asian CV 56.5
final_sample Other CV 66.3

final_sample Hispanic Max 61,436
final_sample Non-Hispanic Black Max 77,065
final_sample Asian Max 49,129
final_sample Other Max 101,795

Re: ODS Excel PROC PRINT style(data)={tagattr="format:###,###"} Not working

pkm_edu — Tue, 29 Aug 2023 16:26:38 GMT

Below is what I got in the Excel file (no decimal place for the CV value (2nd line) and the max values are not comma-separated,

group_cat	racethx	statistic	value_f
final_sample	Hispanic	CV	69.9
final_sample	Non-Hispanic Black	CV	82
final_sample	Asian	CV	56.5
final_sample	Other	CV	66.3

final_sample	Hispanic	Max	61436
final_sample	Non-Hispanic Black	Max	77065
final_sample	Asian	Max	49129
final_sample	Other	Max	101795

Re: ODS Excel PROC PRINT style(data)={tagattr="format:###,###"} Not working

ballardw — Tue, 29 Aug 2023 16:40:27 GMT

Did you consider using the variable Value with the format?

SAS by default right justifies numeric values and I get the desired appearance that way.

proc print data=Test1 noobs blankline=4;
var racethx statistic;
var value;
format value mixedvalue. ;
id group_cat;
run;

I would not expect a character value in SAS to get any "numeric" format from a tagattr to apply very well.

Also that tagattr format if applied to ALL cells would not display any decimals as you don't provide any way for it.

Once upon I time I think I messed with Excel numeric cell formatting to find the instructions for a range similar to your Mixedvalue SAS format but I'm too lazy to bother at this point.

Re: ODS Excel PROC PRINT style(data)={tagattr="format:###,###"} Not working

pkm_edu — Tue, 29 Aug 2023 17:45:50 GMT

/************************************************************************

* style(data)={tagattr="format:###,###"}, etc. deleted

The following gives me the desired results (shown below)

*************************************************************************/

ods listing close;
ods excel file = "C:\Data\test.xlsx"
options (sheet_name = 'Sheet1');
proc print data=Test1 noobs blankline=4;
var racethx statistic;
var value
format value mixedvalue. ;
id group_cat;
run;
ods listing;

group_cat	racethx	statistic	value
final_sample	Hispanic	CV	69.9
final_sample	Non-Hispanic Black	CV	82.0
final_sample	Asian	CV	56.5
final_sample	Other	CV	66.3

final_sample	Hispanic	Max	61,436
final_sample	Non-Hispanic Black	Max	77,065
final_sample	Asian	Max	49,129
final_sample	Other	Max	101,795

How would I avoid repeating the value of the GROUP_CAT variable listed in the ID statement?

Thanks, ballardw.

Re: ODS Excel PROC PRINT style(data)={tagattr="format:###,###"} Not working

ballardw — Tue, 29 Aug 2023 19:56:41 GMT

To change the behavior of ID has limited possibilities depending on actual data and need.

With the example shown, where your ID variable only has one level you can add BY group with the same variable so the ID only gets shown once.

proc print data=Test1 noobs blankline=4;
var racethx statistic;
var value;
format value mixedvalue. ;
id group_cat;
by group_cat;
run;

This is a special case and also suppresses the BY line that would typically appear.

Another option would be to move to proc report and use your group_cat variable as an Order variable and the rest as display variables.

Re: ODS Excel PROC PRINT style(data)={tagattr="format:###,###"} Not working

Tom — Tue, 29 Aug 2023 20:12:38 GMT

If you want to change the Excel formatting (the "tagattr") for individual cells in a column then you will need to use PROC REPORT and not PROC PRINT.

Re: ODS Excel PROC PRINT style(data)={tagattr="format:###,###"} Not working

pkm_edu — Wed, 30 Aug 2023 03:08:39 GMT

Thanks for your suggestions.