Sum values on unique records

[cmemtsa]

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. 

[yabwon · Accepted solution]

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

[cmemtsa]

It needs a subquery!
Thanks for your help.