proc sql -grouping

[Ronein]

Hello friends,

I want to ask if it is possible to use proc sql to calculate summary statistics per  grouping values.

For example:

Team variable has 3 possible values: a,b,c

I want to group the values by using proc format :  values a and b will belong to "a,b'  and value c to  "c"

As you can see in the code output2 is not the desired result.

Data tbl;
input ID Team $  Y;
CARDS;
1 a 2
2 b 4
3 b 6
4 c 8
5 a 10
6 a 12
7 a 14
8 c 16
9 c 18
10 c 20
;
run;
proc format;
Value $Ffmt
'a','b'='a,b'
'c'='c';
run;

PROC SQL;
	create table ouput1 as
	select 	Team ,
            count(*)  as No_Customers 
	from tbl
	group by Team
;
QUIT;


PROC SQL;
	create table ouput2 as
	select 	put(Team,$Ffmt.) as  Team_Category,
            count(*)  as No_Customers 
	from tbl
	group by put(Team,$Ffmt.)
;
QUIT;

[Kurt_Bremser · Accepted solution]

Try this SQL instead:

PROC SQL;
	create table ouput2 as
	select 	put(Team,$Ffmt.) as  Team_Category,
            count(*)  as No_Customers 
	from tbl
	group by calculated Team_Category
;
QUIT;

The SAS SQL compiler does not realize on its own that the expression in the group by is the same that was used for the creation of Team_Category, so you need to rub its nose in it.

[Ronein]

Perfect!!

I just want to under the logic.

Why  the  code:   group by put(Team,$Ffmt.)  is not working??

and your code :group by calculated Team_Category   is working?

They should be equivalent

[Kurt_Bremser]

---

@Ronein wrote:

Perfect!!

I just want to under the logic.

Why  the  code:   group by put(Team,$Ffmt.)  is not working??

and your code :group by calculated Team_Category   is working?

They should be equivalent

---

I already answered that. It's a shortcoming of the SAS SQL compiler. When it evaluates the group by, the expression with the put is a different object(!) than the newly created variable, although the same expression was used to create it, but that fact seems to no longer be available to the SQL compiler logic.

I just added a new idea to the Software Ballot:

https://communities.sas.com/t5/SASware-Ballot-Ideas/Enhance-the-quot-group-by-quot-evaluation-logic-...