topic Re: Proc transpose count in SAS Programming

Proc transpose count

Azeem112 — Sat, 28 Mar 2020 07:50:36 GMT

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,

Re: Proc transpose count

Kurt_Bremser — Sat, 28 Mar 2020 08:37:03 GMT

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 */

Re: Proc transpose count

yabwon — Sat, 28 Mar 2020 11:39:09 GMT

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

Re: Proc transpose count

Kurt_Bremser — Sat, 28 Mar 2020 13:45:15 GMT

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.

Re: Proc transpose count

Tom — Sat, 28 Mar 2020 15:37:09 GMT

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.

Re: Proc transpose count

Azeem112 — Sun, 29 Mar 2020 10:59:34 GMT

Thanks.

Re: Proc transpose count

Azeem112 — Sun, 29 Mar 2020 11:00:52 GMT

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.