topic Re: Sum values on unique records in New SAS User

Sum values on unique records

cmemtsa — Mon, 19 Oct 2020 07:07:48 GMT


data T;
input ID L CF CD EXP;
Datalines;
1 1 A1 AAA 50
1 2 A1 BBB 100
1 4 A2 CCC 200
2 1 A1 AAA 50
2 2 A1 BBB 100
2 4 A2 CCC 200
;

proc sql;
create table wanted as 
select cf, sum (exp) as A
from T
group by distinct cd;
quit;

Hi. In this dataset I want to find to create a table sum of EXP per CF but only per the distinct values of CD i.e. the output should be

A1 150

A2 200

Thank you.

Re: Sum values on unique records

yabwon — Mon, 19 Oct 2020 07:37:21 GMT

Hi,

Do you mean something like this:

data T;
  input ID L CF :$ CD :$ EXP;
Datalines;
1 1 A1 AAA 50
1 2 A1 BBB 100
1 4 A2 CCC 200
2 1 A1 AAA 50
2 2 A1 BBB 100
2 4 A2 CCC 200
;

proc sql;
create table want as 
select cf, sum (exp) as A
from 
  (select distinct CF, CD, EXP from T)
group by cf;
quit;

Bart

Re: Sum values on unique records

cmemtsa — Mon, 19 Oct 2020 07:44:15 GMT

It needs a subquery!
Thanks for your help.