I have three character variables (percent, lower CL, upper CL) that I pulled from a Crosstab table. They are numeric (length = 8, format = 7.4), and when I print out the table, it gives values that have (as expected) 4 decimal places.
I'm trying to change that to 1 decimal place, retaining any trailing zeros so when I run a proc report, all the values are visually even with each other & easier to read.
I've tried a variety of approaches that aren't quite working; I have found something (even if it doesn't make sense...) that gets close to what I want using the code below; the code iterates through a list of variables I previously defined so I don't have to run the code for each variable one at a time. It then concatenates and formats the values for the proc report, printing it out to a Word document.
ODS HTML;
%macro dothisb;
** WORD output, once everything is printed;
ods rtf file="\PATHWAY\Report_Tables..rtf"
startpage=no noKEEPN;
options nodate nonumber orientation=portrait;
TITLE;
%do i=1 %to %sysfunc(countw(&crossnames));
%let thisname=%scan(&crossnames,&i,%str( ));
** Create Simplified crosstabs from earlier proc surveyfreq;
Data year2_&thisname;
Set year_&thisname;
DROP Table intended F_: WgtFreq Percent LowerCL UpperCL _SkipLine;
/* CHAR --> NUMERIC */
Row_Percent=input(RowPercent, 4.1);
Lower_CL=input(RowLowerCL, 4.1);
Upper_CL=input(RowUpperCL, 4.1);
Run;
** Format Simplified tables-- Concatenate % and 95% CLs;
Data year3_&thisname;
Set year2_&thisname;
/* Percent formatting */
Percent=(Row_Percent||"% ");
/* Confidence Interval Formatting */
Confidence=("("||Lower_CL||"-"||Upper_CL||")");
DROP RowLowerCL RowUpperCL ;
Label Percent="%";
Label Confidence="95% CL";
Label combined="% (95% CL)";
Run;
** PROC REPORT;
Proc report data=year3_&thisname out=report_&thisname
nowd
STYLE(report) = [rules=none frame=hsides background=white cellspacing=0 cellpadding=.05]
STYLE(header) = [foreground=black background=white cellheight=.40in
borderbottomwidth=2.0pt borderbottomcolor=black
bordertopwidth=.5pt bordertopcolor=white
borderleftwidth=.5pt borderleftcolor=white
borderrightwidth=.5pt borderrightcolor=white]
;
Column &thisname YY4_DOB,(Percent Confidence) dummyvar;
Define &thisname / order=internal group
style(column) = {vjust=top just=left
borderleftcolor=white borderleftwidth=.5pt
borderrightcolor=white borderrightwidth=.5pt paddingright=.05in};
Define yy4_DOB / "Year" across
style(column) = {vjust=top just=Middle
borderleftcolor=white borderleftwidth=.5pt
borderrightcolor=white borderrightwidth=.5pt paddingleft=.05in};
Define Percent / display
style(column) = {cellheight=.40in cellwidth=0.55in
vjust=top just=left
borderleftcolor=white borderleftwidth=.5pt
borderrightcolor=white borderrightwidth=.5pt paddingright=.05in};
Define Confidence / display
style(column) = {cellheight=.40in cellwidth=0.75in
vjust=top just=middle
borderleftcolor=white borderleftwidth=.5pt
borderrightcolor=black borderrightwidth=.5pt paddingleft=.05in};
Define dummyvar / computed noprint ;
compute dummyvar;
dummyvar = 1;
endcomp;
Run;
%end;
ODS rtf CLOSE;
%mend;
%dothisb
*Invoke;
%dothisb
However, I have some problems. The original values look like this:
Year |
dpinsur |
RowPercent |
RowLowerCL |
RowUpperCL |
2016 |
None |
1.7935 |
0.2168 |
3.3702 |
2016 |
Private |
58.3730 |
53.3669 |
63.3792 |
2016 |
Public |
39.8334 |
34.8675 |
44.7994 |
2017 |
None |
1.8858 |
0.4645 |
3.3071 |
2017 |
Private |
59.7053 |
55.0038 |
64.4067 |
2017 |
Public |
38.4089 |
33.7411 |
43.0767 |
2018 |
None |
0.2916 |
0.0000 |
0.6793 |
2018 |
Private |
65.5822 |
60.9709 |
70.1935 |
2018 |
Public |
34.1263 |
29.5189 |
38.7336 |
2019 |
None |
1.4170 |
0.0000 |
2.9249 |
2019 |
Private |
53.5578 |
47.5044 |
59.6111 |
2019 |
Public |
45.0252 |
38.9781 |
51.0723 |
2020 |
None |
0.6038 |
0.0000 |
1.3675 |
2020 |
Private |
61.9009 |
56.0220 |
67.7798 |
2020 |
Public |
37.4953 |
31.6267 |
43.3639 |
And my proc report sometimes gives values like this, where some values are not being rounded correctly or the zeros from the 95% Confidence limits are not being carried over like I want.
|
Year |
|||||||||
|
2016 |
2017 |
2018 |
2019 |
2020 |
|||||
Insurance Status |
% |
95% CL |
% |
95% CL |
% |
95% CL |
% |
95% CL |
% |
95% CL |
None |
1.79% |
(0.21-3.37) |
1.88% |
( 0- 3.3) |
0.29% |
( .-0.67) |
1.41% |
( .-2.92) |
0.6% |
( .-1.36) |
Private |
58.3% |
(53.3-63.3) |
59.7% |
(55-64) |
65.5% |
(60-70.1) |
53.5% |
(47.5-59.6) |
61% |
(56-67.7) |
Public |
39.8% |
(34.8-44.7) |
38.4% |
(33.7-43) |
34.1% |
(29.5-38.7) |
45% |
(38-51) |
37.4% |
(31.6-43.3) |
Like I've said, I've tried a variety of ways to work around this, but haven't found one that gets it totally right. Any help would be greatly appreciated; I've been tinkering with this for hours.
Since you haven't provided any example data, I can't test this, but I think the problem is here:
Data year3_&thisname;
Set year2_&thisname;
/* Percent formatting */
Percent=(Row_Percent||"% ");
/* Confidence Interval Formatting */
Confidence=("("||Lower_CL||"-"||Upper_CL||")");
DROP RowLowerCL RowUpperCL ;
Label Percent="%";
Label Confidence="95% CL";
Label combined="% (95% CL)";
Run;
where you could format things as in:
Confidence=("("||put(Lower_CL,4.1)||"-"||put(Upper_CL,4.1)||")");
I would also suggest that instead of creating these character strings for PROC REPORT, you use Lower_CL as a numeric variable and just to the right of it in your PROC REPORT, you have Upper_CL as a numeric variable. By using numeric variables here instead of character strings, I think things work out more cleanly and now you don't have to convert numeric to character. In fact, by working with numeric values, you can then use the STYLE= option in PROC REPORT to globally set the format so that the number of decimal places is one (or whatever else you want it to be).
Please consider what you write for descriptions. This bit is contradictory.
I have three character variables (percent, lower CL, upper CL) that I pulled from a Crosstab table.
They are numeric (length = 8, format = 7.4),
A character variable cannot have a format of 7.4. Your code shows that you have three numeric variables that you use to create TWO character variables, Percent and Confidence, which are the ones displayed in Proc Report.
Second do you know what "implied decimal" refers to when used with an Informat?
Row_Percent=input(RowPercent, 4.1);
may not be doing what you think it does for some values. Example:
This code demonstrates some possibly unexpected by you behaviors:
data example; input RowPercent 4.1; datalines; 1 12 123 1234 123.4 12.34 ; proc print data=example; run;
Which yields this result:
Row Obs Percent 1 0.1 2 1.2 3 12.3 4 123.4 5 123.0 6 12.3 When you use an informat such as 4.1 and there is no decimal in the value then you are forcing a decimal into the result.
When the number of characters exceeds 4, observation 5 reading 123.4, then things are truncated to the 4 characters.
Almost certainly you need to use a simple input with 4. or possibly larger field.
If part of your purpose is to align things "nicely" then you better make sure you are using a Fixed width font in the columns as there is no promise that a created text character will display aligned with a proportional font. See below:
1111111111
0000000000
Ten ones and ten zeroes, take up different amounts of space in the default proportional font that is likely displaying this window.
With numeric value you can use one of the format like Dw.p or BestDw.p which will attempt to align values by decimal.
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.