Help using Base SAS procedures

How to apply a format to specific rows/cells of a dataset SAS 9.4

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 14
Accepted Solution

How to apply a format to specific rows/cells of a dataset SAS 9.4

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.

 


Accepted Solutions
Solution
‎06-01-2017 11:02 AM
SAS Employee
Posts: 118

Re: How to apply a format to specific rows/cells of a dataset SAS 9.4

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


All Replies
SAS Employee
Posts: 118

Re: How to apply a format to specific rows/cells of a dataset SAS 9.4

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
SAS Employee
Posts: 118

Re: How to apply a format to specific rows/cells of a dataset SAS 9.4

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;

Occasional Contributor
Posts: 14

Re: How to apply a format to specific rows/cells of a dataset SAS 9.4

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.

SAS Employee
Posts: 118

Re: How to apply a format to specific rows/cells of a dataset SAS 9.4

 
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;
Occasional Contributor
Posts: 14

Re: How to apply a format to specific rows/cells of a dataset SAS 9.4

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

Solution
‎06-01-2017 11:02 AM
SAS Employee
Posts: 118

Re: How to apply a format to specific rows/cells of a dataset SAS 9.4

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;
Occasional Contributor
Posts: 14

Re: How to apply a format to specific rows/cells of a dataset SAS 9.4

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;

SAS Employee
Posts: 118

Re: How to apply a format to specific rows/cells of a dataset SAS 9.4

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.

Super User
Posts: 10,501

Re: How to apply a format to specific rows/cells of a dataset SAS 9.4

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?

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 150 views
  • 1 like
  • 3 in conversation