DATA Step, Macro, Functions and more

Proc SQL Aggregate total not working

Reply
Frequent Contributor
Posts: 131

Proc SQL Aggregate total not working

proc sql;

create table test as

select catg as catg1, sum(apps) as apps, gla,mdy

from test2

group by catg,apps,gla

;quit;

Output

catg           apps                gla          mdy

233             1                     100       01jun2014

233             3                     100      01jun2014

234             5                     50       01jul2014

235            5                     100     01aug2014

 

Aggregate totals not working how can I get this instead

catg           apps                gla          mdy

233             4                    200       01jun2014

234             5                     50       01jul2014

235            5                     100     01aug2014

So in this exmple the 233 should be totalling since the catg repeats

Super User
Posts: 10,538

Re: Proc SQL Aggregate total not working

You will have to show what the input data set looks like. Best is as a data step so there is no question about data type or other characterisitc.

 

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

 

But since your data apparently has values of GLA = 100 then when you group by GLA you are not going to get any sum=200.

Super User
Posts: 17,912

Re: Proc SQL Aggregate total not working

You shouldn't have apps in the select or group statements if you want to summarize that variable. 

 

Remeber to define your grouping variables first and put them in your Select/Group statements and then do you summary statistics.

Ask a Question
Discussion stats
  • 2 replies
  • 100 views
  • 0 likes
  • 3 in conversation