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.
Study | Model Year | Age | All Cost Total |
Study1 | 2015 | 0 | 2,551 |
Study1 | 2015 | 30 | 2,659 |
Study1 | 2015 | 60 | 1,444 |
Study2 | 2015 | 0 | 2,495 |
Study2 | 2015 | 30 | 2,341 |
Study2 | 2015 | 60 | 1,535 |
Study3 | 2015 | 0 | 1,472 |
Study3 | 2015 | 30 | 2,514 |
Study3 | 2015 | 60 | 1,567 |
Study1 | 2014 | 0 | 2,756 |
Study1 | 2014 | 30 | 1,887 |
Study1 | 2014 | 60 | 2,453 |
Study2 | 2014 | 0 | 2,358 |
Study2 | 2014 | 30 | 2,255 |
Study2 | 2014 | 60 | 1,741 |
Study3 | 2014 | 0 | 1,318 |
Study3 | 2014 | 30 | 2,623 |
Study3 | 2014 | 60 | 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
Study | Year | AllCost | Age |
Study1 | 2015 | 4732 | 0 |
Study1 | 2015 | 3306 | 30 |
Study1 | 2015 | 2886 | 60 |
Study1 | 2015 | 3244 | 90 |
Study1 | 2015 | 2845 | 0 |
Study1 | 2015 | 2657 | 30 |
Study1 | 2015 | 2378 | 60 |
Study1 | 2015 | 1226 | 90 |
Study2 | 2015 | 2497 | 0 |
Study2 | 2015 | 2025 | 30 |
Study2 | 2015 | 4785 | 60 |
Study2 | 2015 | 1767 | 90 |
Study3 | 2015 | 1714 | 0 |
Study3 | 2015 | 1835 | 30 |
Study3 | 2015 | 1122 | 60 |
Study3 | 2015 | 2782 | 90 |
Study2 | 2015 | 3831 | 30 |
Study3 | 2015 | 1627 | 0 |
Study3 | 2015 | 2448 | 30 |
Study3 | 2015 | 4790 | 60 |
Study3 | 2015 | 2131 | 90 |
Study1 | 2015 | 4781 | 0 |
Study1 | 2015 | 1055 | 30 |
Study1 | 2015 | 3156 | 60 |
Study1 | 2015 | 3299 | 90 |
Study1 | 2014 | 4174 | 0 |
Study1 | 2014 | 3755 | 30 |
Study1 | 2014 | 1687 | 60 |
Study1 | 2014 | 2434 | 90 |
Study1 | 2014 | 2948 | 0 |
Study1 | 2014 | 1366 | 30 |
Study1 | 2014 | 3946 | 60 |
Study1 | 2014 | 4527 | 90 |
Study2 | 2014 | 2096 | 0 |
Study2 | 2014 | 1880 | 30 |
Study2 | 2014 | 3142 | 60 |
Study2 | 2014 | 3769 | 90 |
Study3 | 2014 | 1674 | 0 |
Study3 | 2014 | 1556 | 30 |
Study3 | 2014 | 3745 | 60 |
Study3 | 2014 | 4311 | 90 |
Study2 | 2014 | 4116 | 60 |
Study2 | 2014 | 3755 | 90 |
Study3 | 2014 | 1514 | 0 |
Study3 | 2014 | 4040 | 30 |
Study3 | 2014 | 1963 | 60 |
Study3 | 2014 | 3005 | 90 |
Study3 | 2014 | 1117 | 0 |
Study1 | 2014 | 3201 | 30 |
Study1 | 2014 | 2690 | 60 |
Study1 | 2014 | 2188 | 90 |
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.
If possible, can you add a sample data of what you have, then it would be easy for someone to help.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.