BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
simkinm2
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
RahulG
Barite | Level 11

 

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

3 REPLIES 3
RahulG
Barite | Level 11

 

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
Calcite | Level 5

Thank you, this worked perfectly.

LinusH
Tourmaline | Level 20

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

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to connect to databases in SAS Viya

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.

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