Is this a bug? [PROC REPORT]

[VX_Xc]

proc report data= abcd MISSING nowindows;

      column a2 (price quant),(a3),MEAN;

      rbreak after / summarize OL;

      define a3 / across;

      define a2 / group;

run;

                                          The SAS System                                     

                                        price                 quant

                                          a3                    a3

                                         a          c          a          c

                       a2             MEAN       MEAN       MEAN       MEAN

                       l         41.516667         55  50.666667         16

                       r             28.22          .       49.2          .

                                 ƒƒƒƒƒƒƒƒƒ  ƒƒƒƒƒƒƒƒƒ  ƒƒƒƒƒƒƒƒƒ  ƒƒƒƒƒƒƒƒƒ

                                  33.20625         55      49.75         16

proc report data= abcd MISSING nowindows;

      column a2 (price quant),(a3 SUM),MEAN;

      rbreak after / summarize OL;

      define a3 / across;

      define a2 / group;

run;

                                          The SAS System                                        10

                                   price                            quant

                               a3                               a3

                              a          c                     a          c

            a2             MEAN       MEAN        SUM       MEAN       MEAN        SUM

            l         41.516667         55     234.55                             36.8

            r             28.22          .      141.1                             49.2

                      ƒƒƒƒƒƒƒƒƒ  ƒƒƒƒƒƒƒƒƒ  ƒƒƒƒƒƒƒƒƒ                        ƒƒƒƒƒƒƒƒƒ

                       33.20625         55     375.65                               43

First PROC works fine but for second PROC...where are my mean quantity values for each level of variable a3?

Only difference between two PROCs is inclusion of keyword SUM. This must be a bug right (it worked well for price)?

[Ksharp]

What does your sample look like? what ouptut do you need?

I do not think is a bug. Your syntax is not regular.

column a2 (price quant),(a3),MEAN;

means

column a2 a3,(price,MEAN quant,MEAN);

but order of a3 and price quant is different.

column a2 (price quant),(a3 SUM),MEAN;

means

column a2  a3,(price,mean quant,mean) (price,sum quant,sum) ;

That is what your output is.

Ksharp

(price,mean quant,mean)(price,mean quant,mean)

[VX_Xc]

Shouldn’t;

column a2 (price quant),(a3 SUM),MEAN;

mean;

column a2 price,a3,MEAN price,sum,MEAN quant,a3,MEAN quant,sum,MEAN;

?

[VX_Xc]

data abcd;

      infile cards DLM = ',';

      input name $ (a1-a3) ($) price quant;

      cards;

s,m,l,c,70,20

a,m,l,a,33.55,54

a,g,l,a,60,32

o,m,r,a,20,68

an,m,l,c,40,12

h,m,r,a,20,44

i,m,r,a,20.1,12

k,m,r,a,35,22

r,g,r,a,46,100

b,m,l,a,31,66

;

run;

Using the data above.

proc report

      data= abcd MISSING nowindows;

      column a2 (price quant),(a3 SUM),MEAN;   

      rbreak after / summarize OL;

      define a3 / across;

      define a2 / group;

run;

Above process is equivalent to;

proc report

      data= abcd MISSING nowindows;

      column a2 (price,a3,MEAN price,sum,MEAN quant,a3,MEAN quant,sum,MEAN);

      rbreak after / summarize OL;

      define a3 / across;

      define a2 / group;

run;

Which results in an error, so first process should really give out an error too. But instead it did something like below;

proc report

      data= abcd MISSING nowindows;

      column a2 (price,a3,MEAN price,sum quant,a3,MEAN quant,sum);

      rbreak after / summarize OL;

      define a3 / across;

      define a2 / group;

run;

But with no printed values for quant,a3,MEAN.

I’m just trying to understand the syntaxes. I don’t understand why SAS does this for this particular example. What am I missing?

[Ksharp · Accepted solution]

It is very interesting. When I run your code ,I also get no error. But if you change the (a3 sum ),(price quant).

You will get Error.

proc report

      data= abcd MISSING nowindows ;

      column a2 (a3 sum ),(price quant),mean;  

      rbreak after / summarize OL;

      define a3 / across;

      define a2 / group;

run;

I think the most reason is your syntax of report is not regular.

As normal ,we will put categorical variable before numeric variable.

If you run the code below. you will also get the same output as yours without compile error.  Weird?

proc report

      data= abcd MISSING nowindows ;

      column a2 (price quant),(a3 sum);  

      rbreak after / summarize OL;

      define a3 / across;

      define a2 / group;

run;

If you use

proc report

      data= abcd MISSING nowindows ;

      column a2 (price quant),a3 ;  

      rbreak after / summarize OL;

      define a3 / across;

      define a2 / group;

run;

That will be OK.

If you use

proc report

      data= abcd MISSING nowindows ;

      column a2 (price quant),(a3 a1);  

      rbreak after / summarize OL;

      define a3 / across;

      define a1/across;

      define a2 / group;

run;

will get the same as yours.

So, This tell me that you can't use this syntax to more than one categorical variable.

If you need more than one categorical variables then change their order.

proc report

      data= abcd MISSING nowindows ;

      column a2 (a3 a1),(price quant);  

      rbreak after / summarize OL;

      define a3 / across;

      define a1/across;

      define a2 / group;

run;

So in a word, The regular syntax is our best choice. We should avoid to use this strange syntax.

Ksharp

[VX_Xc]

Thank you~