SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Summing vertically and transposing results

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

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: 266

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
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.

View solution in original post


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

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
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.

Occasional Contributor
Posts: 15

Re: Summing vertically and transposing results

Thank you, this worked perfectly.

Super User
Posts: 5,429

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 409 views
  • 1 like
  • 3 in conversation