DATA Step, Macro, Functions and more

PROC SQL - Sum over categorical variables

Reply
Contributor
Posts: 27

PROC SQL - Sum over categorical variables

[ Edited ]

An interesting question:

I've got table as follows where the variables are as follows:

ID GROUP1 GROUP2 TYPE1 YEAR COUNT AMOUNT

where ID is a primary key.

I wish to create a table where the ID PK is dropped and where only the variables GROUP1 GROUP2 TYPE1 YEAR COUNT

AMOUNT appear but where  COUNT AMOUNT consist of the sum over the categorical variables (GROUP1 GROUP2 TYPE1 YEAR).

 

In this case, for every combination of the values of each categorical variables, I have the sum of COUNT and the sum of AMOUNT.

Any idea how to go about with this??

 

Thank you!!

 

 

Regular Contributor
Posts: 242

Re: PROC SLQ - Sum over categorical variables

proc sql;
create table want as
select  GROUP1 ,GROUP2 ,TYPE1, YEAR,
sum( COUNT), sum( AMOUNT)
from have
group by GROUP1 ,GROUP2 ,TYPE1, YEAR;
quit;
Super User
Posts: 17,963

Re: PROC SQL - Sum over categorical variables

Few quick rules that can help:

*Grouping variables go in group by statement.

*All other variables need to be an aggregate function. 

*Items appearing in select are either from group by (  not required) or an aggregation  

Super User
Posts: 17,963

Re: PROC SQL - Sum over categorical variables


Reeza wrote:

Few quick rules that can help:

*Grouping variables go in group by statement.

*All other variables need to be an aggregate function. 

*Items appearing in select are either from group by (  not required) or an aggregation  


There's exceptions to these rules in SAS SQL but working from this will help you avoid issues. 

Ask a Question
Discussion stats
  • 3 replies
  • 362 views
  • 7 likes
  • 3 in conversation