BookmarkSubscribeRSS Feed
scb
Obsidian | Level 7 scb
Obsidian | Level 7

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;

3 REPLIES 3
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
ChrisBrooks
Ammonite | Level 13

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;

 

scb
Obsidian | Level 7 scb
Obsidian | Level 7

Thanks a lot.  Both answers is acceptable.

hackathon24-white-horiz.png

The 2025 SAS Hackathon Kicks Off on June 11!

Watch the live Hackathon Kickoff to get all the essential information about the SAS Hackathon—including how to join, how to participate, and expert tips for success.

YouTube LinkedIn

What is Bayesian Analysis?

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 3301 views
  • 2 likes
  • 3 in conversation