SAS Query

[scb]

data a;
input id $ a $ b $ valid $ Amt;
CARDS;
110 Y Y Y 1000
110 Y N Y 1000
110 N N Y 100
111 Y Y N 788
111 N N N 100
111 N Y Y 119
;
RUN;

PROC SQL;
CREATE TABLE B AS SELECT DISTINCT
ID,
CASE WHEN A EQ 'Y' THEN SUM(AMT) ELSE 0 END AS A,
CASE WHEN B EQ 'Y' THEN SUM(AMT) ELSE 0 END AS B
FROM A WHERE VALID EQ 'Y'
GROUP BY ID, A, B
ORDER BY ID;
QUIT;

My desired output:

ID       A      B

110    2000 1000

111    0         119

Anyone can help? Thanks.

[stat_sas]

Hi,

Try this.

PROC SQL;
CREATE TABLE B AS
select ID,
sum(CASE WHEN A EQ 'Y' THEN AMT ELSE 0 END) AS A,
sum(CASE WHEN B EQ 'Y' THEN AMT ELSE 0 END) AS B
FROM A WHERE VALID EQ 'Y'
GROUP BY ID
ORDER BY ID;
QUIT;

[Haikuo]

proc sql;
create table want as
select id,sum(amt*(a='Y')*(valid='Y')) as a, sum(amt*(b='Y')*(valid='Y')) as b from a
group by id;
quit;