Date:         Sun, 29 Oct 2006 22:51:08 -0500
Reply-To:     "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
Subject:      Re: Proc Transpose Question

On Sun, 29 Oct 2006 22:38:28 -0500, Howard Schreier <hs AT dc-sug DOT org>
<nospam@HOWLES.COM> wrote:

>On Sun, 29 Oct 2006 18:45:29 -0800, Xuhong <zhuxuhong2000@GMAIL.COM> wrote:
>
>>I have question about the proc transpose. I simplified my dataset like
>>below layout. What I want is to transpose the data but need the
>>student's name as the label for the transposed variables.
>>Does anybody have good method to handle it?
>>
>>Student Name  testnum score
>>
>>Capalleti        1   94
>>Dubose         1    51
>>Engles        1    95
>>Capalleti     2    99
>>Dubose      2    65
>>Engles       2    94
>>Capalleti     3   92
>>Dubose      3   87
>>Engles       3   90
>>
>>...
>>
>>testnum Capalleti  Dubose Engles
>>1   94   51   95
>>2   99   65   94
>>3   92   87   90
>>
>>...
>
>It's surprisingly difficult.
>
>I loaded the sample data:
>
>   data before;
>   informat Student_Name $9.; input
>Student_Name  testnum score; cards;
>
>Capalleti        1   94
>Dubose         1    51
>Engles        1    95
>Capalleti     2    99
>Dubose      2    65
>Engles       2    94
>Capalleti     3   92
>Dubose      3   87
>Engles       3   90
>   ;
>
>I'm assuming that the word "label" in the problem statement is used
>deliberately, and that the new data set should have sequential numerically
>suffixed variable *names* (STUDENT_1, STUDENT_2, etc.), with surnames as the
>variable *labels*. That's certainly a reasonable thing to do.
>
>So I ran this code.
>
>   proc transpose data=before out=after(drop=_name_) prefix=Student_;
>   by testnum;
>   var score;
>   idlabel Student_Name;
>   run;

Here's a possibility: Insert

   id      Student_Name;

before the IDLABEL statement. Then the variable names will be of the form
"Student_Capalleti", so that they can be collectively referenced as
"STUDENT_ : " for arrays and so forth. The labels will be the simple
surnames, as requested.

>
>The transpose took place, but the labels are blank. It is true that the
>documentation says that the IDLABEL statement must follow an ID statement.
>But ...
>
>1. Why? The specification I used seems reasonable and unambiguous.
>
>2. How come there is not so much as a NOTE in the log indicating that the
>IDLABEL is being ignored?
>
>So, it seems that one has to set up some kind of reverse lookup, using an
>informat or a hash or something else, and assign an explicit serial number
>to each student. That added variable can be coded in an ID statement.