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
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.
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?
/* 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
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.
/************************************************************************
* 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.
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.
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.
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 | |
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.