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>
>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;
Here's a possibility: Insert
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.
>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.