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

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:

 

 

filterFallSpringTotal
19609901572
20.0967449360.1081730770.138967468
3217215310
4384339723
5599455838015
60.6040511940.6100305940.708539604
7288126113699
80.2903355840.2852928320.326997878
9636697
107781158
11101525
12101626

 

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
WarrenKuhfeld
Rhodochrosite | Level 12
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;

View solution in original post

9 REPLIES 9
WarrenKuhfeld
Rhodochrosite | Level 12

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
WarrenKuhfeld
Rhodochrosite | Level 12

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;

jpagitt
Obsidian | Level 7

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.

WarrenKuhfeld
Rhodochrosite | Level 12
 
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;
jpagitt
Obsidian | Level 7

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).

WarrenKuhfeld
Rhodochrosite | Level 12
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;
jpagitt
Obsidian | Level 7

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;

WarrenKuhfeld
Rhodochrosite | Level 12

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.

ballardw
Super User

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?

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!

What is Bayesian Analysis?

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.

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
  • 9 replies
  • 2749 views
  • 1 like
  • 3 in conversation