Help using Base SAS procedures

0 instead of missing in SQL

Reply
Frequent Contributor
Frequent Contributor
Posts: 78

0 instead of missing in SQL

[ Edited ]

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;

Super User
Super User
Posts: 9,599

Re: proc sql query

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
Valued Guide
Posts: 593

Re: proc sql query

[ Edited ]

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;

 

Frequent Contributor
Frequent Contributor
Posts: 78

Re: proc sql query

Posted in reply to ChrisBrooks

Thanks a lot.  Both answers is acceptable.

Ask a Question
Discussion stats
  • 3 replies
  • 247 views
  • 2 likes
  • 3 in conversation