topic Re: proc sql query in SAS Procedures

0 instead of missing in SQL

scb — Tue, 17 Oct 2017 09:27:33 GMT

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;

Re: proc sql query

RW9 — Tue, 17 Oct 2017 08:15:44 GMT

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

Re: proc sql query

ChrisBrooks — Tue, 17 Oct 2017 08:19:48 GMT

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;

Re: proc sql query

scb — Mon, 23 Oct 2017 11:10:29 GMT

Thanks a lot. Both answers is acceptable.