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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.