topic proc sql -grouping in SAS Programming

proc sql -grouping

Ronein — Thu, 30 Aug 2018 06:08:18 GMT

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;

Re: proc sql -grouping

Kurt_Bremser — Thu, 30 Aug 2018 06:42:36 GMT

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.

Re: proc sql -grouping

Ronein — Thu, 30 Aug 2018 06:45:09 GMT

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

Re: proc sql -grouping

Kurt_Bremser — Thu, 30 Aug 2018 06:56:16 GMT

@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-of-the-SQL/idi-p/491111