proc sql query

[scb]

When I tried to run the below code, I would like  to count the number of distinct product (except product “ABE”) for every ID, but it turned out all product are equal to 1. 

My desired output would be:

ID         Product          Amount

123         3                    90000

124         2                   170000

125         2                    36180

Anyone can help? Thanks.

data test;
input product $ id $ amt;
datalines;
ABC 123 10000
ABD 123 20000
ABD 123 40000
ABE 123 10000
ABC 124 10000
ABD 124 20000
ABC 124 60000
ABE 124 80000
ABC 125 13000
ABD 125 23180
;
run;

proc sql;
create table TEST1 as
select ID,
sum(DISTINCT case when PRODUCT="ABE" then 0 else 1 end) as PRODUCT,
sum(AMT) as AMT
from TEST
group by ID;
quit;

[LaurieF]

Given that I think there's a discrepancy in your desired output, does this work?

proc sql;
create table test1 as
   select test.id,
          product_count.count as product,
          sum(test.amt) as amt
     from test as test
     left join (select id,
                       count(distinct product) as count
                  from test
                 where product ne 'ABE'
                 group by id) as product_count
       on test.id = product_count.id
    group by test.id;
quit;

[Ksharp]

data test;
input product $ id $ amt;
datalines;
ABC 123 10000
ABD 123 20000
ABD 123 40000
ABE 123 10000
ABC 124 10000
ABD 124 20000
ABC 124 60000
ABE 124 80000
ABC 125 13000
ABD 125 23180
;
run;
proc sql;
select ID,
(select count(distinct product) from test where id=a.id and product ne 'ABE') as PRODUCT,
sum(AMT) as AMT
from TEST as a
group by ID;
quit;