topic Re: SAS Query in SAS Programming

SAS Query

scb — Wed, 13 Sep 2017 11:27:40 GMT

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.

Re: SAS Query

stat_sas — Wed, 13 Sep 2017 11:51:37 GMT

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;

Re: SAS Query

Haikuo — Wed, 13 Sep 2017 14:50:38 GMT


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;