Proc transpose count

[Azeem112]

I've a table that looks like below

   Sr     Q1   Q2     Q3 Q4 Q5 ....... Q31

   1       A      B       C    D   A              C

   2       B      D      C    A    B              D

   3      C      A       B    D    C             B

I've removed the Sr from this table and I want a frequency analysis basesd on questions that how many times an option was chosen from a question

I want to convert this information as below

Question  A   B  C  D

   Q1         1  1   1   0

   Q2         1   1   0  1

   Q3         0   1   2   0

    .

    .

    Q31     0    1    1   1

I can use proc freq and save the result in HTML or excel using ods but I'm looking for an approach to use proc transpose to count and create the dataset exactly as described above so I can use it in reports.

I tried

proc transpose data=sample;

var q1 q2 q3 q4 ..... q31;

run.

I don't' have a by variable here so I'm having difficulties in finding an approach to do it. 

Thanks,

[Kurt_Bremser]

From my tablet, so I can't provide code.

Do your transpose as you posted.

Run a summary with

class _name_ col1;

Transpose again with

by _name_;
id col1;
var count; /* the summary result */

[yabwon]

Hi,

Why not to use an informat and datastep+array?

data have;
input Sr Q1 $ Q2 $ Q3 $ Q4 $ Q5 $ Q6 $;
cards;
1 A B C D A C
2 B D C A B D
3 C A B D C B
;
run;

proc format;
 invalue ABCD
 A=1
 B=2
 C=3
 D=4
 ;
run;

data want;
  set have end = eof;

  array Q[*] Q:;
  array ABCDbyQ[4,6] _temporary_ (%eval(4*6)*0);

  length QUESTION $ 10;
  array ABCD A B C D;

  do i = 1 to dim(Q);
    ABCDbyQ[input(Q[i],ABCD.), i] + 1; 
  end;

  if EOF then
    do i = lbound2(ABCDbyQ) to hbound2(ABCDbyQ);
      do j = lbound1(ABCDbyQ) to hbound1(ABCDbyQ);
        QUESTION = vname(Q[i]);
        ABCD[j] = ABCDbyQ[j,i]; 
      end;
      output;
    end;
  keep QUESTION A B C D;
run;
proc print;
run;

All the best

Bart

[Azeem112]

Thanks for it. It also works fine but I'd like to avoid the double loop as there are many columns and hundreds of rows of data.

[Kurt_Bremser · Accepted solution]

Now with code:

data have;
input Sr Q1 $ Q2 $ Q3 $ Q4 $ Q5 $ Q6 $;
cards;
1 A B C D A C
2 B D C A B D
3 C A B D C B
;

proc transpose data=have out=trans;
by sr;
var Q:;
run;

proc summary data=trans nway;
class _name_ col1;
output out=sum;
run;

proc transpose data=sum out=want (rename=(_name_=question));
by _name_;
id col1;
var _freq_;
run;

Thanks to @yabwon for providing data in usable form. Please do always provide example data in this way, as it makes it much easier to test code.

[Azeem112]

Thanks.

[Tom]

You need to include

by sr ;

in your PROC TRANSPOSE step so it transposes N matrices of 1 row by 31 columns instead of just one matrix of N rows by 31 columns.