A problem with proc transpose

[deleted_user]

Dear All,

First of all I need to thank everyone here whos posts I've used to help me learn more about SAS. At the moment I am building some applications to help me create tables out of a dataset. However, a problem with proc transpose arose on which I need your help.

Let's say that I've made this crossed table (results over 2 questions), where there is no-one who awnsered question 1 with a 2 AND question 2 with a 2. I get this table:

Q1 Q2 freq
1 1 2
1 2 2
1 3 3
2 1 2
2 3 1
3 1 2
3 2 2
3 3 3

Using:

proc transpose data = ... out = ... prefix = vari;
BY Q1;
var freq;
run;

I get this table:

Q2 vari1 vari2 vari3
1 2 2 3
2 2 1 .
3 2 2 3

Where the "." should actually be on (Q2 = 2, vari2); instead the 1 from (Q2=2,vari3) is placed there.

How can I overcome this problem? Is there a simple command for it? Or should I go back to the proc freq I used to create the first table and solve the problem there?

Any help would be welcome! Have a nice day

[sbb]

Please review your own code. The SAS PROC PRINT output you show in your post could not have been generated by the PROC TRANSPOSE code that you provided. There would not be a Q2 variable output by TRANSPOSE, unless you supply that variable in the BY statement list. You asking for assistance and guidance while not truly revealing the code and results that relate to one another.

Scott Barry
SBBWorks, Inc.

[deleted_user]

You are right, the by statement had Q2 instead of Q1.

proc transpose data = ... out = ... prefix = vari;
BY Q2;
var freq;
run;

is my correct code. I was simplifying the names of the variables before posting it here, and I made an error.

[Olivier]

Hi Vince.
I think that you are lacking an ID statement in your Transpose procedure. This will allow SAS to connect newly created variables to Q2 values, instead of just re-arranging values by Q1 values as it currently does.
Could you try this code and tell us if this is what you are looking for ?
(I assumed that the output table you posted add the Q1 variable as the first column, and not "Q2", as Scott pointed out.)
[pre]
DATA test ;
INPUT Q1 Q2 freq ;
CARDS ;
1 1 2
1 2 2
1 3 3
2 1 2
2 3 1
3 1 2
3 2 2
3 3 3
;
RUN ;
PROC TRANSPOSE DATA = test OUT = test2 ;
VAR freq ;
BY q1 ;
ID q2 ;
RUN ;
[/pre]
Regards
Olivier

[deleted_user]

Oliver,

That did the trick, thank you! And Scott, I will be more carefull with my next post, thank you.