topic Re: proc tabulate with classdata statement in SAS Programming

proc tabulate with classdata statement

Ronein — Mon, 11 Mar 2019 06:32:34 GMT

Hello

I am using proc tabulate to create a summary table.

I want to display also categories that have no values in the raw data .

In this example I want to see also category "0<-0.5" that has no values in the raw data.

For this task I am using classdata statement.

However, I get an error " Class variable Y_Category was not found in the preload data set or was incompatible with the primary data set variable of the same name.*


Data rawdata;
Input ID Y;
cards;
1 12000
2 0
3 0.6
4 0.7
5 19
6 90
7 200
8 400
9 800
10 2000
11 4000
12 9000
13 0
;
run;

proc format;
value FFmt
0='0'
0<-0.5='0<-0.5'
0.5<-20='0.5<-20'
20<-10000='5000<-10000'
10000-high='10000+';
Run;

data tbl2;
set rawdata;
Y_Category=put(Y,FFmt.);
format Y_Category $20.;
Run;
 

Data level;
Input Y_Category $20.;
cards;
0
0<-0.5
5000<-10000
10000+
;
Run;

proc sort data=tbl2;by y;run;
 proc tabulate data=tbl2  classdata=level ;
var Y;
class Y_Category/ORDER=data  MISSING;
table Y_Category='',Y=''*N='Customers'
            Y=''*PCTN='PCT from Total Customers'
			Y=''*SUM='Sum Y'
            Y=''*COLPCTSUM='PCT from total Y' /RTS=25 PRINTMISS BOX='category of Y' CONDENSE;
Run;

 /*ERROR: Class variable Y_Category was not found in the preload data set or was incompatible with the primary data set variable of 
       the same name.*/

Re: proc tabulate with classdata statement

andreas_lds — Mon, 11 Mar 2019 07:12:55 GMT

This is the result of your posted code:

-----------------------------------------------------------------------------
|category of Y          |            |  PCT from  |            |            |
|                       |            |   Total    |            |  PCT from  |
|                       | Customers  | Customers  |   Sum Y    |  total Y   |
|-----------------------+------------+------------+------------+------------|
|0                      |        2.00|       15.38|        0.00|        0.00|
|-----------------------+------------+------------+------------+------------|
|0<-0.5                 |        0.00|        0.00|           .|           .|
|-----------------------+------------+------------+------------+------------|
|5000<-10000            |        7.00|       53.85|    16490.00|       57.84|
|-----------------------+------------+------------+------------+------------|
|10000+                 |        1.00|        7.69|    12000.00|       42.09|
|-----------------------+------------+------------+------------+------------|
|0.5<-20                |        3.00|       23.08|       20.30|        0.07|
-----------------------------------------------------------------------------

Ignoring the strange sort-order (caused by using a alphanumeric variable), what's the problem?

Re: proc tabulate with classdata statement

Ronein — Mon, 11 Mar 2019 07:28:34 GMT

When I run the proc tabulate I get an error "Class variable Y_Category was not found in the preload data set or was incompatible with the primary data set variable of the same name"

 proc tabulate data=tbl2  classdata=level ;
var Y;
class Y_Category/ORDER=data  MISSING;
table Y_Category='',Y=''*N='Customers'
            Y=''*PCTN='PCT from Total Customers'
			Y=''*SUM='Sum Y'
            Y=''*COLPCTSUM='PCT from total Y' /RTS=25 PRINTMISS BOX='category of Y' CONDENSE;
Run;

Re: proc tabulate with classdata statement

Kurt_Bremser — Mon, 11 Mar 2019 07:28:43 GMT

Maxim 3: Know Your Data.

Look at Y_category in tbl2 and in level.

Since the maximum string length in your format is 11, Y_Category is defined in tbl2 with that length (the format does NOT change that!)

Reading it with a length of 20 for level causes the incompatibility.

Optimize your two data steps:

data tbl2;
set rawdata;
Y_Category = put(Y,FFmt.);
run;
 
data level;
input Y_Category $11.;
cards;
0
0<-0.5
5000<-10000
10000+
;
run;

and the following code will run without problems:

27         data tbl2;
28         set rawdata;
29         Y_Category = put(Y,FFmt.);
30         run;

NOTE: There were 13 observations read from the data set WORK.RAWDATA.
NOTE: The data set WORK.TBL2 has 13 observations and 3 variables.
NOTE: DATA statement used (Total process time):
      real time           0.03 seconds
      cpu time            0.01 seconds
      

31         
32         data level;
33         input Y_Category $11.;
34         cards;

NOTE: The data set WORK.LEVEL has 4 observations and 1 variables.
NOTE: DATA statement used (Total process time):
      real time           0.07 seconds
      cpu time            0.00 seconds
      
39         ;

40         run;
41         
42         proc sort data=tbl2;
43         by y;
44         run;

NOTE: There were 13 observations read from the data set WORK.TBL2.
2                                                          Das SAS System                               07:43 Monday, March 11, 2019

NOTE: The data set WORK.TBL2 has 13 observations and 3 variables.
NOTE: PROZEDUR SORT used (Total process time):
      real time           0.04 seconds
      cpu time            0.00 seconds
      

45         
46         proc tabulate data=tbl2 classdata=level ;
47         var Y;
48         class Y_Category / order=data missing;
49         table Y_Category='',Y=''*N='Customers'
50               Y=''*PCTN='PCT from Total Customers'
51         			Y=''*SUM='Sum Y'
52               Y=''*COLPCTSUM='PCT from total Y' / rts=25 printmiss box='category of Y' condense;
53         run;

NOTE: There were 13 observations read from the data set WORK.TBL2.
NOTE: There were 4 observations read from the data set WORK.LEVEL.
NOTE: The PROCEDURE TABULATE printed page 1.
NOTE: PROZEDUR TABULATE used (Total process time):
      real time           0.12 seconds
      cpu time            0.01 seconds

Re: proc tabulate with classdata statement

andreas_lds — Mon, 11 Mar 2019 07:30:45 GMT

My fault, i automatically removed the format-statement in the data step creating tbl2.

You don't need classdata at all, just use preloadfmt:

data rawdata;
   Input ID Y;
   cards;
1 12000
2 0
3 0.6
4 0.7
5 19
6 90
7 200
8 400
9 800
10 2000
11 4000
12 9000
13 0
;
run;

proc format;
   value FFmt
      0='0'
      0<-0.5='0<-0.5'
      0.5<-20='0.5<-20'
      20<-10000='5000<-10000'
      10000-high='10000+';
run;

data work.cloned;
   set rawdata;

   Y_Category = y;

   format y_category ffmt.;

run;


proc tabulate data=cloned missing;
   var Y;
   class Y_Category / order=data preloadfmt missing ;
   
   table Y_Category='',Y=''*N='Customers'
      Y=''*PCTN='PCT from Total Customers'
      Y=''*SUM='Sum Y'
      Y=''*COLPCTSUM='PCT from total Y' /RTS=25 PRINTMISS BOX='category of Y' CONDENSE;
run;

Re: proc tabulate with classdata statement

Kurt_Bremser — Mon, 11 Mar 2019 07:36:19 GMT

@andreas_lds wrote:

You don't need classdata at all, just use preloadfmt:

Which is the better option, anyway.

Re: proc tabulate with classdata statement

Ronein — Mon, 11 Mar 2019 08:48:25 GMT

Perfect!

Thank you.

Two more question please.

I see that the order of the categories in the output is not as I want .

I want it to be in the following order:

0
0<-0.5
5000<-10000
10000+

What should I do in order to get it?

I also want to ask Why "Sum Y" and "PCT from total Y" in category "0<-0.5" is getting null value (.) and not 0??

Re: proc tabulate with classdata statement

Kurt_Bremser — Mon, 11 Mar 2019 08:51:50 GMT

Make your dataset LEVEL complete. As posted, it only has 4 observations, while the format has 5 entries.

Re: proc tabulate with classdata statement

Ronein — Mon, 11 Mar 2019 08:58:33 GMT

perfect

I also want to ask Why "Sum Y" and "PCT from total Y" in category "0<-0.5" is getting null value (.) and not 0??

Re: proc tabulate with classdata statement

Ronein — Mon, 11 Mar 2019 09:02:08 GMT

Sorry. I don't understand what you mean

Re: proc tabulate with classdata statement

Kurt_Bremser — Mon, 11 Mar 2019 09:05:14 GMT

Still better: use the preloadfmt option, and have a separate format for your class variable that you use in the preloadfmt:

data rawdata;
input ID Y;
cards;
1 12000
2 0
3 0.6
4 0.7
5 19
6 90
7 200
8 400
9 800
10 2000
11 4000
12 9000
13 0
;
run;

proc format;
value category
  0="0"
  0<-0.5="1"
  0.5<-20="2"
  20<-10000="3"
  10000-high="4"
;
value FFmt
  0='0'
  1='0<-0.5'
  2='0.5<-20'
  3='5000<-10000'
  4='10000+'
;
run;

data tbl2;
set rawdata;
Y_Category = input(put(Y,category.),1.);
format y_category ffmt.;
run;

proc tabulate data=tbl2;
var Y;
class Y_Category / order=data preloadfmt;
table
  Y_Category='',
  Y=''*N='Customers'
  Y=''*PCTN='PCT from Total Customers'
	Y=''*SUM='Sum Y'
  Y=''*COLPCTSUM='PCT from total Y'
  / rts=25 printmiss box='category of Y' condense
;
run;

The first format does the conversion, the second serves to create the complete list.

Result:

-----------------------------------------------------------------------------
|category of Y          |            |  PCT from  |            |            |
|                       |            |   Total    |            |  PCT from  |
|                       | Customers  | Customers  |   Sum Y    |  total Y   |
|-----------------------+------------+------------+------------+------------|
|0                      |        2.00|       15.38|        0.00|        0.00|
|-----------------------+------------+------------+------------+------------|
|0<-0.5                 |        0.00|        0.00|           .|           .|
|-----------------------+------------+------------+------------+------------|
|0.5<-20                |        3.00|       23.08|       20.30|        0.07|
|-----------------------+------------+------------+------------+------------|
|5000<-10000            |        7.00|       53.85|    16490.00|       57.84|
|-----------------------+------------+------------+------------+------------|
|10000+                 |        1.00|        7.69|    12000.00|       42.09|
-----------------------------------------------------------------------------

Because you use class (and not by), no sorting is needed

Re: proc tabulate with classdata statement

Kurt_Bremser — Mon, 11 Mar 2019 09:07:19 GMT

@Ronein wrote:

Sorry. I don't understand what you mean

Compare your original proc format code (5 entries) with your data step code for dataset level (4 entries). The 5th category from the format which is not found in level is appended at the end of the tabulate output.

Re: proc tabulate with classdata statement

Kurt_Bremser — Mon, 11 Mar 2019 09:11:02 GMT

@Ronein wrote:

perfect

I also want to ask Why "Sum Y" and "PCT from total Y" in category "0<-0.5" is getting null value (.) and not 0??

Because no entries are found, the summation ends up as sum(.), and that creates a missing value.

For reference, try this:

data test;
x1 = sum(.);
x2 = sum(0,.);
run;

You need at least 1 non-missing value for the summation to be non-missing. Counts, OTOH, create a zero.

Re: proc tabulate with classdata statement

Ronein — Mon, 11 Mar 2019 09:34:48 GMT

Thank you so much.