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!!
proc sql;
create table want as
select GROUP1 ,GROUP2 ,TYPE1, YEAR,
sum( COUNT), sum( AMOUNT)
from have
group by GROUP1 ,GROUP2 ,TYPE1, YEAR;
quit;
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
@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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.