Hello everyone,
I am attempting to put the final touches on a quick report I was working on and am running into an issue with certain rows of a report not being in the format I want them in. My dataset looks like this:
filter | Fall | Spring | Total |
1 | 960 | 990 | 1572 |
2 | 0.096744936 | 0.108173077 | 0.138967468 |
3 | 217 | 215 | 310 |
4 | 384 | 339 | 723 |
5 | 5994 | 5583 | 8015 |
6 | 0.604051194 | 0.610030594 | 0.708539604 |
7 | 2881 | 2611 | 3699 |
8 | 0.290335584 | 0.285292832 | 0.326997878 |
9 | 63 | 66 | 97 |
10 | 77 | 81 | 158 |
11 | 10 | 15 | 25 |
12 | 10 | 16 | 26 |
The numbers in filter row 2, 6, and 8 are percentages that I originally had in percent8.2 format prior to a union step where I added all of these rows together. I attempted to maintain the format within the proc sql step where I had multiple unions, but I couldn't make it stick. I found a thread on here where someone had a similar issue and was able to solve their problem via proc report, but I have yet to be able to make it work. Below is the code that I have been using in this step and it does not give any error messages, but it also just stays the same as the code above.
proc report data=combined_all out=combined_all2;
column filter fall spring total;
define filter / display;
define fall / display;
define spring / display;
define total / display;
compute total;
if filter in (2,6,8) then do;
do i= 2 to 4;
call define(i,'format','percent8.2');
end;
end;
endcomp;
run;
I appreciate any help that I can get.
Sorry! That was me being fast and sloppy! Appending the data was harmless but silly.
This is what I should have said.
data x; input Filter Fall Spring Total; datalines; 1 960 990 1572 2 0.096744936 0.108173077 0.138967468 3 217 215 310 4 384 339 723 5 5994 5583 8015 6 0.604051194 0.610030594 0.708539604 7 2881 2611 3699 8 0.290335584 0.285292832 0.326997878 9 63 66 97 10 77 81 158 11 10 15 25 12 10 16 26 ; data y(drop=fall spring total); set x; length f s t $ 8; f = ifc(fall le 1, put(fall, percent8.2), put(fall, 7.0)); s = ifc(spring lt 1, put(spring, percent8.2), put(spring, 7.0)); t = ifc(total lt 1, put(total, percent8.2), put(total, 7.0)); label f='Fall' s='Spring' t='Total'; run; proc print noobs label style(data header)={just=right}; run;
For a simple ad hoc report, you could do something like this. There may be PROC REPORT solutions, but I don't know about that.
data x(drop=fall spring total); input Filter Fall Spring Total; length f s t $ 8; f = ifc(filter in (2, 6, 8), put(fall, percent8.2), put(fall, 7.0)); s = ifc(filter in (2, 6, 8), put(spring, percent8.2), put(spring, 7.0)); t = ifc(filter in (2, 6, 8), put(total, percent8.2), put(total, 7.0)); label f='Fall' s='Spring' t='Total'; datalines; 1 960 990 1572 2 0.096744936 0.108173077 0.138967468 3 217 215 310 4 384 339 723 5 5994 5583 8015 6 0.604051194 0.610030594 0.708539604 7 2881 2611 3699 8 0.290335584 0.285292832 0.326997878 9 63 66 97 10 77 81 158 11 10 15 25 12 10 16 26 ; proc print noobs label; run;
Filter Fall Spring Total 1 960 990 1572 2 9.67% 10.82% 13.90% 3 217 215 310 4 384 339 723 5 5994 5583 8015 6 60.41% 61.00% 70.85% 7 2881 2611 3699 8 29.03% 28.53% 32.70% 9 63 66 97 10 77 81 158 11 10 15 25 12 10 16 26
One more thing. For destinations like html and rtf you will want to right justify.
proc print noobs label style(data header)={just=right}; run;
Warren,
Thank you for the reply. This is actually going to be an annual request and if it really came down to it I could use a datalines approach, but I'd like for the script to be set up in a way where nothing would need to be changed from year to year unless there was an update to the actual definitions of the data request. I am still pretty new to base SAS, so if there is a different way that I could go about using this same technique without having to manually enter the data back in, then that would be great. I don't know if that would come from an infile statement or what.
You don't have to enter the data back in. Here assume your initial SAS data set is X.
For you it would be whatever. Just specify the data set name in the SET statement.
Then I read it into a DATA step using a SET statement to do the condiditonal formatting.
I changed the logic below to use the percent format form values lt 1. You will need to ensure
that you use the right logic for your case. I hope this helps.
data x; input Filter Fall Spring Total; datalines; 1 960 990 1572 2 0.096744936 0.108173077 0.138967468 3 217 215 310 4 384 339 723 5 5994 5583 8015 6 0.604051194 0.610030594 0.708539604 7 2881 2611 3699 8 0.290335584 0.285292832 0.326997878 9 63 66 97 10 77 81 158 11 10 15 25 12 10 16 26 ; data y(drop=fall spring total); set x; length f s t $ 8; f = ifc(fall le 1, put(fall, percent8.2), put(fall, 7.0)); s = ifc(spring lt 1, put(spring, percent8.2), put(spring, 7.0)); t = ifc(total lt 1, put(total, percent8.2), put(total, 7.0)); label f='Fall' s='Spring' t='Total'; datalines; 1 960 990 1572 2 0.096744936 0.108173077 0.138967468 3 217 215 310 4 384 339 723 5 5994 5583 8015 6 0.604051194 0.610030594 0.708539604 7 2881 2611 3699 8 0.290335584 0.285292832 0.326997878 9 63 66 97 10 77 81 158 11 10 15 25 12 10 16 26 ; proc print noobs label style(data header)={just=right}; run;
I apologize in advance, but I don't understand your solution. In your example I understand the first data step where you are creating the original data set. You then follow it with another data step using a set statement to reference the data set, but also include datalines within the second data step once again explicitly referencing all of the data. What I don't understand is why the datalines within the second data step is necessary. I know that when I attempt to run the formatting data step without datalines I receive an error saying that an infile or datalines must be present. I have tried working with an infile statement, but have been unsuccessful in getting that to work. If manually entering the data in the datalines portion of the formatting data set is necessary for this solution to work, then I am not sure if this is the solution I am looking for.
I am not sure if I am just looking at this the wrong way. That is entirely possible (and more than likely probable).
Sorry! That was me being fast and sloppy! Appending the data was harmless but silly.
This is what I should have said.
data x; input Filter Fall Spring Total; datalines; 1 960 990 1572 2 0.096744936 0.108173077 0.138967468 3 217 215 310 4 384 339 723 5 5994 5583 8015 6 0.604051194 0.610030594 0.708539604 7 2881 2611 3699 8 0.290335584 0.285292832 0.326997878 9 63 66 97 10 77 81 158 11 10 15 25 12 10 16 26 ; data y(drop=fall spring total); set x; length f s t $ 8; f = ifc(fall le 1, put(fall, percent8.2), put(fall, 7.0)); s = ifc(spring lt 1, put(spring, percent8.2), put(spring, 7.0)); t = ifc(total lt 1, put(total, percent8.2), put(total, 7.0)); label f='Fall' s='Spring' t='Total'; run; proc print noobs label style(data header)={just=right}; run;
It is now working as intended! Thanks, Warren. The code I used is below.
data y(drop=fall spring total);
set x;
length f s t $8;
f=ifc(filter in (2, 6, 8), put(fall, percent8.2), put(fall, 7.0));
s=ifc(filter in (2, 6, 8), put(spring, percent8.2), put(spring, 7.0));
t=ifc(filter in (2, 6, 8), put(total, percent8.2), put(total, 7.0));
label f='Fall' s='Spring' t='Total';
run;
Great! Sorry again about the slightly silly intermediate posting. BTW, some of the tables that you see in SAS procedure output are constructed in a somewhat similar way. These tables are called factoids. One or two columns might contain a mix of character and numeric values, and the numeric values might be formatted in different ways. ODS actually shows you character variables that were made by differential formatting. Typically the output data set also contains a numeric variable that provides the underlying values for the numeric rows. PROCs use C code to do this and not DATA steps, but the idea is the same. You can't assign multiple formats to a single variable in a SAS DATA step, but you can apply multiple formats when you render numeric values into a character variable.
A $0.02 contribution for what it may be worth:
@jpagitt wrote:
Hello everyone,
I am attempting to put the final touches on a quick report I was working on and am running into an issue with certain rows of a report not being in the format I want them in. My dataset looks like this:
The numbers in filter row 2, 6, and 8 are percentages that I originally had in percent8.2 format prior to a union step where I added all of these rows together. I attempted to maintain the format within the proc sql step where I had multiple unions, but I couldn't make it stick. I found a thread on here where someone had a similar issue and was able to solve their problem via proc report, but I have yet to be able to make it work. Below is the code that I have been using in this step and it does not give any error messages, but it also just stays the same as the code above.
A reason why a column should only contain the same type of data: count, rate, measurement or whatever. When you mix summary calculations with record values in the same column then you almost always later on have to back them out again or some other complex operation. The report procedures available in SAS are often very cabable of providing the summaries as needed and seldom need to have the data as you have forced it.
As a further consideration what happens when you mix units of measure in the same column? If some records are count-of-people and others are people-per-100000 population (or similar) what happens in modeling procedures or even simple means?
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.