The result is what i wanted but I would like to have 0 instead of . ; any idea? Thanks.
data have;
input id product $ amount;
cards;
111 A 1000
111 A 1200
111 B 1000
222 B 3000
222 B 88
;
run;
PROC SQL;
CREATE TABLE have1 AS SELECT DISTINCT
A.ID, A.PRODUCT,
(select SUM(AMOUNT) from HAVE
where ID=a.ID and PRODUCT EQ 'A') as A_RATE,
(select SUM(AMOUNT) from HAVE
where ID=a.ID and PRODUCT EQ 'B') as B_RATE
FROM HAVE AS A
GROUP BY A.ID
ORDER BY A.ID;
QUIT;
Case when is the conditional in SQL:
(select case when sum(AMOUNT)=. then 0 else sum(AMOUNT) end from HAVE where ID=a.ID and PRODUCT EQ 'A') as A_RATE
Instead of just sum(amount) use coalesce(sum(amount,0)) as below - coalesce returns the first non-missing result of its arguments
PROC SQL;
CREATE TABLE have1 AS SELECT DISTINCT
A.ID, A.PRODUCT,
(select coalesce(SUM(AMOUNT),0) from HAVE
where ID=a.ID and PRODUCT EQ 'A') as A_RATE,
(select coalesce(SUM(AMOUNT),0) from HAVE
where ID=a.ID and PRODUCT EQ 'B') as B_RATE
FROM HAVE AS A
GROUP BY A.ID
ORDER BY A.ID;
QUIT;
Thanks a lot. Both answers is acceptable.
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 the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.