Date:         Sun, 15 Apr 2007 22:28:47 -0400
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, reorg data

On Fri, 13 Apr 2007 14:48:55 -0700, david5705@HOTMAIL.COM wrote:

[snip]

>> >> >Dear All:
>>
>> >> >I wish to reorganize the following data (input data) and probably
>> >> >using PROC TRANSPOSE. How would I write the code so that I can report
>> >> >up to 70 groups of data (70th record for a store within a single
>> >> >zipcode, i.e., a group = variables v1, v2, v3) representing 3
>> >> >variables for a maximum of 70 stores within a zip code (i.e.,
>> >> >represented in the desired output as v1_70, v2_70, v3_70, or similar
>> >> >labeling)?
>>
>> >> >Thanks in advance,
>>
>> >> >David
>>
>> >> >(1) Input data
>>
>> >> >zipcode  store_id  v1  v2  v3
>> >> >00001    1            A   B   C
>> >> >00001    2            A   B   C
>> >> >00002    5            B   C   D
>> >> >00003    6            A   B   C
>> >> >00003    7            C   D   E
>> >> >00003    9            A   B   C
>> >> >00004   12           E   F   G
>> >> >...
>>
>> >> >(2) Output data
>>
>> >> >zipcode   v1_1  v2_1  v3_1  v1_2  v2_2  v3_2   v1_3  v2_3
>> >> >v3_3...v1_70  v2_70  v3_70
>> >> >00001     A      B      C       A      B      C
>> >> >00002     B      C      D
>> >> >00003     A      B      C       C      D      E       A       B      C
>> >> >00004     E      F      G
>> >> >...

>All:
>
>Thank you for your generous comments - I have all that I need - and I
>was able to generate the output in the format I wanted. One of our
>external statisticians that we retain to assist us in governance
>matters requested that I provide him with data using the format I
>described in the desired "output data" example.
>
>David
>Philadelphia, PA

Since the request did not include exporting the wide data set, I presume
that the consultant will be working with SAS.

Just keep in mind that a consensus of SAS-L's best and brightest considers
such complex and wide data structures to be a Bad Thing.

Nevertheless, I'll throw in my solution. It is a hybrid, using both of the
tools which are useful for this sort of task: arrays and PROC TRANSPOSE.
This code does all of its work in one step (no intermediate data sets are
created; just a view).

Data a;
informat zipcode $5.   store_id 8.  v1  v2  v3 $1.;
input zipcode  store_id  v1  v2  v3;
cards;
00001    1            A   B   C
00001    2            A   B   C
00002    5            B   C   D
00003    6            A   B   C
00003    7            C   D   E
00003    9            A   B   C
00004   12            E   F   G
;

   data fortranspose(keep = zipcode _name_ v) / view=fortranspose;
   set a;
   by zipcode;
   array vv(3) v1-v3;
   if first.zipcode then obsnum = 0;
   obsnum + 1;
   do i = 1 to 3;
      _name_ = 'v' || put(i,1.) || '_' || put(obsnum,5. - L);
      v = vv(i);
      output;
      end;
   run;

   proc transpose data=fortranspose out=b(drop=_name_);
   by zipcode;
   var v;
   run;