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.
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: