topic Re: proc sql query in SAS Programming

proc sql query

scb — Mon, 20 Mar 2017 09:13:42 GMT

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;

Re: proc sql query

LaurieF — Mon, 20 Mar 2017 09:37:27 GMT

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;

Re: proc sql query

Ksharp — Mon, 20 Mar 2017 10:19:21 GMT


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;