Date:         Tue, 13 Jul 2004 13:51:57 -0400
Reply-To:     Howard Schreier <Howard_Schreier@ITA.DOC.GOV>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Howard Schreier <Howard_Schreier@ITA.DOC.GOV>
Subject:      Re: Multiple Proc transpose question

Here's one way. It requires that all of the variables to be transposed be
of the same type.

Sample initial dataset:

   data have;
   input id a b c;
   cards;
   1 111 112 113
   1 121 122 123
   2 211 212 213
   ;

Begin by transposing all three variables at once:

   proc transpose data=have out=flip;
   by id;
   var a b c;
   run;

The result:

   Obs    id    _NAME_    COL1    COL2

    1      1      a        111     121
    2      1      b        112     122
    3      1      c        113     123
    4      2      a        211       .
    5      2      b        212       .
    6      2      c        213       .

Now it turns out that the trickiest part is knowing the "width" of the
arrays; that is, the number of COLx variables. It is 2 here because there
were 2 or fewer observations in each ID group. With real data there may be
more.

We can get the number from the metadata:

   proc sql noprint;
   select put(count(distinct name),5.) into : awidth
    from dictionary.columns
    where libname='WORK' and memname='FLIP' and name eqt 'COL';
   quit;

   %let awidth = &AWIDTH;

The %LET is there only to gety rid of leading blanks in the value of
&AWIDTH.

Finally, merge the components:

   data want(drop=_name_);
   merge
    flip(where=(_name_='a') rename=(col1-col&AWIDTH = a1-a&AWIDTH) )
    flip(where=(_name_='b') rename=(col1-col&AWIDTH = b1-b&AWIDTH) )
    flip(where=(_name_='c') rename=(col1-col&AWIDTH = c1-c&AWIDTH) )
    ;
   by id;
   run;

The end result:

   id     a1     a2     b1     b2     c1     c2

    1    111    121    112    122    113    123
    2    211      .    212      .    213      .

On Mon, 12 Jul 2004 21:10:31 +0100, =?iso-8859-1?q?taran=20Singh?=
<taran_jit@YAHOO.COM> wrote:

>Hi folks:
>
>I am transposing a dataset   and i need to transpose  it for three
different values . How can i accomplish  that with one proc transpose  with
control on the names .
>
>this is what i am doing :
>
> proc transpose data = SKU_CSV_REPORT out = test1(drop = _name_ _label_)
prefix = size  ;
> by style_color_mkt week_num;
> id size_code ;
>  var stockout_percent ;
>  run;
>
> proc transpose data = SKU_CSV_REPORT out = test11(drop = _name_
_label_) ) prefix = size_s  ;
> by style_color_mkt week_num;
> id size_code ;
> var sku_stk ;
> run;
> proc transpose data = SKU_CSV_REPORT out = test13(drop = _name_ _label_))
prefix = size_s  ;
> by style_color_mkt week_num;
> id size_code ;
> var sku_total ;
>run;
>and i am merging the output data sets .
>
>Thanks
>
>Taran
>
>
>
>---------------------------------
> ALL-NEW Yahoo! Messenger - sooooo many all-new ways to express yourself