DATA Step, Macro, Functions and more

SQL sum by multiple group

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

SQL sum by multiple group

[ Edited ]

I am trying to sum a metric (AllCost) by three different variables, Age, Study Type, and Year.

 

So the final table would look something like this. 

 

 

StudyModel YearAgeAll Cost Total
Study120150                         2,551
Study1201530                         2,659
Study1201560                         1,444
Study220150                         2,495
Study2201530                         2,341
Study2201560                         1,535
Study320150                         1,472
Study3201530                         2,514
Study3201560                         1,567
Study120140                         2,756
Study1201430                         1,887
Study1201460                         2,453
Study220140                         2,358
Study2201430                         2,255
Study2201460                         1,741
Study320140                         1,318
Study3201430                         2,623
Study3201460                         1,300


 

proc sql ;
create table work.temp002 as select 
distinct a.study, a.model_year, a.Claims, sum(AllCost) as AllCostTotal from work.temp001 a where Age in(0:10000) group by a.Age and a.Study and a.model_year; quit; run;

 

 

The output I am getting: AllCostTotal is the same number for all studies and years and changes when age changes from 0 to 60, etc.

 

Anything you guys can do to help would be greatly appreciated. 

 

Best,

 

JKeags23 

 

 

EDIT: Sample Data

 

StudyYearAllCostAge
Study1201547320
Study12015330630
Study12015288660
Study12015324490
Study1201528450
Study12015265730
Study12015237860
Study12015122690
Study2201524970
Study22015202530
Study22015478560
Study22015176790
Study3201517140
Study32015183530
Study32015112260
Study32015278290
Study22015383130
Study3201516270
Study32015244830
Study32015479060
Study32015213190
Study1201547810
Study12015105530
Study12015315660
Study12015329990
Study1201441740
Study12014375530
Study12014168760
Study12014243490
Study1201429480
Study12014136630
Study12014394660
Study12014452790
Study2201420960
Study22014188030
Study22014314260
Study22014376990
Study3201416740
Study32014155630
Study32014374560
Study32014431190
Study22014411660
Study22014375590
Study3201415140
Study32014404030
Study32014196360
Study32014300590
Study3201411170
Study12014320130
Study12014269060
Study12014218890

 


Accepted Solutions
Solution
‎06-28-2017 01:43 PM
Super User
Posts: 11,343

Re: SQL sum by multiple group

What happens if you change

group by a.Age and a.Study and a.model_year

 

to

 

group by a.Age , a.Study , a.model_year

 

 

The 'And' as you are using it creates a boolean evaluation for the three variables and since they are all likely not to equal 0 (false) you only get one result: true so there is only one group.

View solution in original post


All Replies
PROC Star
Posts: 326

Re: SQL sum by multiple group

If possible, can you add a sample data of what you have, then it would be easy for someone to help.

Solution
‎06-28-2017 01:43 PM
Super User
Posts: 11,343

Re: SQL sum by multiple group

What happens if you change

group by a.Age and a.Study and a.model_year

 

to

 

group by a.Age , a.Study , a.model_year

 

 

The 'And' as you are using it creates a boolean evaluation for the three variables and since they are all likely not to equal 0 (false) you only get one result: true so there is only one group.

New Contributor
Posts: 2

Re: SQL sum by multiple group

Such a simple, yet eloquent solution.

Thank you, Grand Advisor ballardw!
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 163 views
  • 1 like
  • 3 in conversation