Date:         Wed, 5 Jul 2006 06:42:16 -0400
Reply-To:     Jim Groeneveld <jim2stat@YAHOO.CO.UK>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Jim Groeneveld <jim2stat@YAHOO.CO.UK>
Subject:      Re: Transposing data
Comments: To: Nuno Soares <ns.mlists@GMAIL.COM>

Hi Nuno,

It is my impression that this question is posed almost weekly. So it is
easy to have a look in the SAS-L archives if you don't read SAS-L
regularly. There you will also find recommendations of mine.

Download MR2RM.zip with MR2RM.sas inside from
http://home.hccnet.nl/jim.groeneveld/software/SASmacro and also follow
instructions in MR2RM.txt

DATA Extended;
  SET YourData;
  IF (Variable EQ 'X1') THEN IndexVar=1;
  ELSE IF (Variable EQ 'X2') THEN IndexVar=2;
  ELSE IF (Variable EQ 'X3') THEN IndexVar=3;
RUN;

%MR2RM (Data=Extended, Out=Flattened, OverWrit=Y, ByList=Year Comp Name,
        VarList=Data, Index=IndexVar);

Regards - Jim.
--
Jim Groeneveld, Netherlands
Statistician, SAS consultant
home.hccnet.nl/jim.groeneveld

My computer predicts: David C. will warn "You don't want to do this".

On Wed, 5 Jul 2006 11:23:32 +0100, Nuno Soares <ns.mlists@GMAIL.COM> wrote:

>Hi everyone,
>
>I'm trying to transpose from long to wide a database that has the
following
>structure;
>
>Year    Compid  Name    Variable        Data
>1980    C1              COMP1   X1              100
>1980    C1              COMP1   X2              10
>1980    C1              COMP1   X3              1
>1980    C2              COMP2   X1              200
>1980    C2              COMP2   X2              20
>1980    C2              COMP2   X3              2
>1981    C1              COMP1   X1              300
>...
>
>I'm using the following code;
>
>proc transpose data=database out=databsetrnsp;
>by year compid name;
>var data;
>id variable;
>run;
>
>The idea is to have the data in the following structure:
>Year    compid  name    x1      x2      x3
>1980    C1              COMP1   100     10      1
>1980    C2              COMP2   200     20      2
>1981    C1              COMP1   ...
>
>Can anyone give me an suggestions of how to do this?
>
>Best,
>
>Nuno