DATA Step, Macro, Functions and more

SAS Query

Reply
Contributor scb
Contributor
Posts: 69

SAS Query

 

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.

Trusted Advisor
Posts: 1,231

Re: SAS Query

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;

Respected Advisor
Posts: 3,156

Re: SAS Query


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;
Ask a Question
Discussion stats
  • 2 replies
  • 101 views
  • 1 like
  • 3 in conversation