**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;