Summing vertically and transposing results

[simkinm2]

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.

[RahulG · Accepted solution]

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.

[simkinm2]

Thank you, this worked perfectly.

[LinusH]

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