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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.