Date:         Mon, 3 Sep 2007 15:04:55 -0400
Reply-To:     Arthur Tabachneck <art297@NETSCAPE.NET>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Arthur Tabachneck <art297@NETSCAPE.NET>
Subject:      Re: Restructuring a data set
Comments: To: Jenny Holm <jennykbh@GMAIL.COM>

Jenny,

If you have more than one set of records, then neither Howard's nor AJS's
solutions appear to provide the ID numbers that you are seeking.

I modified Howard's code as follows:

data have;
/* Note: given the limited width of sas-l posts, the longer data
   records will probably appear as multiple lines */

  informat x1-x21 $3.;
  input id x1-x21;
  cards;
1 A1 A2 A3 B1 B2 B3 C1 C2 C3 D1 D2 D3 E1 E2 E3 F1 F2 F3 G1 G2 G3
2 A4 A5 A6 B4 B5 B6 C4 C5 C6 D4 D5 D6 E4 E5 E6 F4 F5 F6 G4 G5 G6
3 A7 A8 A9 B7 B8 B9 C7 C8 C9 D7 D8 D9 E7 E8 E9 F7 F8 F9 G7 G8 G9
4 A10 A11 A12 B10 B11 B12 C10 C11 C12 D10 D11 D12 E10 E11 E12 F10 F11 F12
G10 G11 G12
5 A13 A14 A15 B13 B14 B15 C13 C14 C15 D13 D14 D15 E13 E14 E15 F13 F14 F15
G13 G14 G15
6 A16 A17 A18 B16 B17 B18 C16 C17 C18 D16 D17 D18 E16 E17 E17 F16 F17 F17
G16 G17 G18
7 A19 A20 A21 B19 B20 B21 C19 C20 C21 D19 D20 D21 E19 E20 E21 F19 F20 F21
G19 G20 G21
8 A22 A23 A24 B22 B23 B24 C22 C23 C24 D22 D23 D24 E22 E23 E24 F22 F23 F24
G22 G23 G24
9 A25 A26 A27 B25 B26 B27 C25 C26 C27 D25 D26 D27 E25 E26 E27 F25 F26 F27
G25 G26 G27
10 A28 A29 A30 B28 B29 B30 C28 C29 C30 D28 D29 D30 E28 E29 E30 F28 F29 F30
G28 G29 G30
;

/*The three pieces of information which SAS cannot readily detect are:
  the number of columns in each "clump", the number of items per record,
  and the total number of rows per set. So provide the three values */

%let number_of_columns=3;
%let number_of_items_per_record=21;
%let number_of_rows_per_set=5;

data long (keep = newrow newcol value);
  set have;
  array aa(*) x1-x&number_of_items_per_record.;
  do j = 1 to dim(aa);
    newrow = ((ceil(_n_/&number_of_rows_per_set.)-1)*
               (&number_of_items_per_record./&number_of_columns.)) +
                 ceil (j /&number_of_columns.);
    newcol = (&number_of_columns *
     (_n_ - 1) +
       mod (j - 1, &number_of_columns) + 1) -
        ((ceil(_n_/&number_of_rows_per_set. - 1) *
          &number_of_rows_per_set.*&number_of_columns.));
    value = aa(j);
    output;
  end;
run;

proc sort data=long;
  by newrow newcol;
run;

proc transpose data=long out=to (drop = newrow _name_) prefix=x;
  by newrow;
  id newcol;
  var value;
run;

data want;
  retain id;
  set to;
  id=_n_;
run;

HTH,
Art
---------
On Sat, 1 Sep 2007 04:48:45 -0400, Jenny Holm <jennykbh@GMAIL.COM> wrote:

>Hi,
>I'm having major difficulties in resturcturing a data set from:
>
>ID x1 x2 x3   x4 x5 x6... x21
>1  1  2  3    16 17 18
>2  4  5  6    19 20 21
>3  7  8  9    22 23 24
>4  10 11 12   25 26 27
>5  13 14 15   28 29 30
>
>to:
>
>ID x1 x2 x3 x4 x5 x6 x7 x8 x9 x10 x11 x12 x13 x14 x15
>1  1  2  3  4  5  6  7  8  9  10  11  12  13  14  15
>2  16 17 18 19 20 21 22 23 24 25  26  27  28  29  30
>...
>7
>
>The variable names are irrelevant to me- I am trying to change the order
of
>the data. The closest I've come to any success is by using a sql outer
>union-but then I end up with to many rows. I have to repeat this task for
>>60 data sets, so I would appreciate any suggestions!
>
>Thank you,
>Jenny