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.
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 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.