## Summing vertically and transposing results

Solved
Occasional Contributor
Posts: 18

# Summing vertically and transposing results

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.

Accepted Solutions
Solution
‎08-10-2016 09:12 AM
Super Contributor
Posts: 271

## Re: Summing vertically and transposing results

``````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
GROUP BY DEPT;
QUIT;

PROC TRANSPOSE DATA=TEMP OUT=TRANS ;
ID DEPT;
RUN;``````

I have used P as Postivie and N as Negative.

All Replies
Solution
‎08-10-2016 09:12 AM
Super Contributor
Posts: 271

## Re: Summing vertically and transposing results

``````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
GROUP BY DEPT;
QUIT;

PROC TRANSPOSE DATA=TEMP OUT=TRANS ;
ID DEPT;
RUN;``````

I have used P as Postivie and N as Negative.

Occasional Contributor
Posts: 18

## Re: Summing vertically and transposing results

Thank you, this worked perfectly.

Super User
Posts: 5,853

## Re: Summing vertically and transposing results

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

Data never sleeps
☑ This topic is solved.