Date:         Fri, 13 Apr 2007 06:51:39 -0400
Reply-To:     Nathaniel.Wooding@DOM.COM
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Nat Wooding <Nathaniel.Wooding@DOM.COM>
Subject:      Re: PROC TRANSPOSE, reorg data
Comments: To: david5705@HOTMAIL.COM
In-Reply-To:  <1176437886.514513.152000@o5g2000hsb.googlegroups.com>
Content-Type: text/plain; charset="US-ASCII"

Here is a Proc Transpose solution that will work without modification for
any number of stores within a zipcode. Gerhard's array solution will
probably be faster but with my clumsy, dyslexic fingers, I try to keep my
coding as simple as possible.

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

Proc transpose out=a;** normalize the data;
      var v:  ;
      by zipcode store_id;
run;

Data a;
    set;
      _name_=compress(_name_||'_'||store_id);

Proc transpose out=a (drop=_name_);
       by zipcode;
run;

proc print;
run;


If you do not want to use the store id as the new variable's subscript, you
could add a counter variable to the second data step that would simply
number each store in each group as 1, 2, 3, ...



Nat Wooding
Environmental Specialist III
Dominion, Environmental Biology
4111 Castlewood Rd
Richmond, VA 23234
Phone:804-271-5313, Fax: 804-271-2977



             david5705@HOTMAIL
             .COM
             Sent by: "SAS(r)                                           To
             Discussion"               SAS-L@LISTSERV.UGA.EDU
             <SAS-L@LISTSERV.U                                          cc
             GA.EDU>
                                                                   Subject
                                       PROC TRANSPOSE, reorg data
             04/13/2007 12:18
             AM


             Please respond to
             david5705@HOTMAIL
                   .COM






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
...



-----------------------------------------
CONFIDENTIALITY NOTICE:  This electronic message contains
information which may be legally confidential and/or privileged and
does not in any case represent a firm ENERGY COMMODITY bid or offer
relating thereto which binds the sender without an additional
express written confirmation to that effect.  The information is
intended solely for the individual or entity named above and access
by anyone else is unauthorized.  If you are not the intended
recipient, any disclosure,  copying, distribution, or use of the
contents of this information is prohibited and may be unlawful.  If
you have received this electronic  transmission in error, please
reply immediately to the sender that you have received the message
in error, and delete it.  Thank you.