Hello,
I have a dataset that looks like this:
Department Question 1 Question 2
A Positive Negative
B Positive Positive
A Negitive Positive
C Negative Negative
D Positive Positive
I would like to get the percent of "positive" per question for each deparment and transpose the results so they look like this:
A B C D
Question 1 55% 76% 99% 67%
Question 2 66% 78% 88% 99%
Any help is greatly appreciated!
Thank you.
DATA REPLY;
INPUT DEPT $ QUEST1 $ QUEST2 $;
DATALINES;
A P N
B P N
C N P
A P N
B N N
C P P
;
PROC SQL;
CREATE TABLE TEMP AS
SELECT DEPT,
ROUND(SUM(CASE WHEN QUEST1 ='P' THEN 1 ELSE 0 END ) / COUNT(*),.01) AS QUEST_1,
ROUND(SUM(CASE WHEN QUEST2 ='P' THEN 1 ELSE 0 END)/ COUNT(*),.01) AS QUEST_2
FROM REPLY
GROUP BY DEPT;
QUIT;
PROC TRANSPOSE DATA=TEMP OUT=TRANS ;
ID DEPT;
RUN;
I have used P as Postivie and N as Negative.
DATA REPLY;
INPUT DEPT $ QUEST1 $ QUEST2 $;
DATALINES;
A P N
B P N
C N P
A P N
B N N
C P P
;
PROC SQL;
CREATE TABLE TEMP AS
SELECT DEPT,
ROUND(SUM(CASE WHEN QUEST1 ='P' THEN 1 ELSE 0 END ) / COUNT(*),.01) AS QUEST_1,
ROUND(SUM(CASE WHEN QUEST2 ='P' THEN 1 ELSE 0 END)/ COUNT(*),.01) AS QUEST_2
FROM REPLY
GROUP BY DEPT;
QUIT;
PROC TRANSPOSE DATA=TEMP OUT=TRANS ;
ID DEPT;
RUN;
I have used P as Postivie and N as Negative.
Thank you, this worked perfectly.
When you transpose data, you change the structure to it, so it can be used by other programs.
But id you wish to change the structure for a specific report, let the reporting procedure/application handle the layout.
Your "want" layout is a report, not a data structure.
As for your "have" data, that is a candidate for transposing, since it's normalized, and therefore not fit for use for most reporting/programming queries.
So the general advice, transpose "have" to a structure with columns Department, Question_no and "reply".
The fix the percentage layout in a reporting procedure, such as tabulate or report
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.