Ya:

Works fine this way(when you create the table first), may be it is looking for the first group by variable already available to the SQL routine, in your case value 1 for each iteration is being generated simultaneously, I don't know if that is the right explanation.

Prasad Ravi

data xx; input x $ idv $ y; cards; c a 1 e a 1 k a 1 c b 1 e b 1 ; options formdlim=' ' nodate nonumber; title;

proc sql; create table new as select 1 as cat0, x, idv, count(distinct x) as msn from xx group by cat0, idv ; select cat0, idv, count(distinct x) as msn from new group by cat0, idv ; select 1 as cat0, idv, count(distinct x) as msn from xx group by idv, cat0 ; select y as cat0, idv, count(distinct x) as msn from xx group by cat0, idv ; quit;

"Huang, Ya" <yhuang@AMYLIN.COM> To: SAS-L@LISTSERV.UGA.EDU Sent by: "SAS(r) cc: Discussion" Subject: A little mystery of proc sql group by <SAS-L@LISTSERV.UGA.E DU>

05/13/2003 08:37 AM Please respond to "Huang, Ya"

Hi there,

Would someone gives a good explanation why the following code generates different result?

data xx; input x $ idv $ y; cards; c a 1 e a 1 k a 1 c b 1 e b 1 ; options formdlim=' ' nodate nonumber; title;

proc sql; select 1 as cat0, idv, count(distinct x) as msn from xx group by cat0, idv ; select 1 as cat0, idv, count(distinct x) as msn from xx group by idv, cat0 ; select y as cat0, idv, count(distinct x) as msn from xx group by cat0, idv ; -------------- cat0 idv msn ffffffffffffffffffffffffffff 1 a 3 1 b 0

cat0 idv msn ffffffffffffffffffffffffffff 1 a 3 1 b 2

cat0 idv msn ffffffffffffffffffffffffffff 1 a 3 1 b 2

Note that msn is different between the first select and the other two. The only difference between first select and second one is the order of cat0 and idv in the group by clause. I've never thought that the order of vars in a group by clause matters.

Thanks

Ya Huang