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.
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
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.