topic Re: Summing vertically and transposing results in SAS Data Management

Summing vertically and transposing results

simkinm2 — Wed, 10 Aug 2016 04:07:06 GMT

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.

Re: Summing vertically and transposing results

RahulG — Wed, 10 Aug 2016 04:57:39 GMT

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.

Re: Summing vertically and transposing results

LinusH — Wed, 10 Aug 2016 07:42:33 GMT

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

Re: Summing vertically and transposing results

simkinm2 — Wed, 10 Aug 2016 13:13:23 GMT

Thank you, this worked perfectly.