BookmarkSubscribeRSS Feed
scb
Obsidian | Level 7 scb
Obsidian | Level 7

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;

 

2 REPLIES 2
LaurieF
Barite | Level 11

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
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 357 views
  • 0 likes
  • 3 in conversation