BookmarkSubscribeRSS Feed
Pyrite | Level 9


I have a variable x1 that range from 0 to 1 and a variable x2 that range from -10 to +10. I want to create a crosstable showing the average of x2 for different ranges of x, such as 

x1  average of x2
0-0.1 #
0.1-0.2 #
0.9-1 #


I know I could easily split the variable x1 in a data statement, but I wonder if it this possible to split in within the proc tabulate statement.


You can, but you have to define a format first to identify the splits.  For example:


proc format;
   value tenths 
   0-0.1 = '0.0-0.1'
   0.1-0.2 = '0.1-0.2'
   0.2-0.3 = '0.2-0.3'
   0.3-0.4 = '0.3-0.4'
   0.4-0.5 = '0.4-0.5'
   0.5-0.6 = '0.5-0.6'
   0.6-0.7 = '0.6-0.7'
   0.7-0.8 = '0.7-0.8'
   0.8-0.9 = '0.8-0.9'
   0.9-1 = '0.9-1.0'

Having done that, you can apply the format within PROC TABULATE:


proc tabulate data=have;
   class x1;
   var x2;
   tables x1, x2*mean;
   format x1 tenths.;
Super User

Being a mathematician, I cringe when I don't know which interval an end-point value occurs in, or even if it appears in any. So if X has a value of exactly 0.1 should it be considered in the 0.0-0.1 range or the 0.1 -0.2 range?


@Astounding's format will place the value of 0.1 in the 0.1 -0.2 range.

Pyrite | Level 9
My example was just theoritical, so it doesn't matter.
Super User

@Demographer wrote:
My example was just theoritical, so it doesn't matter.


I will politely beg to differ. Assignment to categories for analysis may be critical, especially if you have lots of values exactly on the endpoints of intervals. Especially in a group working environment. If you do an analysis with 0.1 in the 0.1 -0.2 group and another team assigns it to the 0.0-0.1 you will have fun reconciling things like N's not matching in different places, or model results if the categorical assignment is used in a class type variable.


You may also have to meet an external client or even government agency's definition and failure to do so could put you or your organization in a number of binds.



Pyrite | Level 9
I mean, this was not a real example, so I don't mind if it's 0-0.1[ or 0-0.01]. I just wanted to know if there is a code to do it automatically in proc tabulate. Of course, when I'll code it, I will be cautious that categories fit.


Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.


Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 5 replies
  • 3 in conversation