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;
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;
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;
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.