Date:         Fri, 13 Apr 2007 07:10:53 -0400
Reply-To:     Gerhard Hellriegel <gerhard.hellriegel@T-ONLINE.DE>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Gerhard Hellriegel <gerhard.hellriegel@T-ONLINE.DE>
Subject:      Re: PROC TRANSPOSE, reorg data

Hi Nat,
something like that was also my first idea, because my fingers are somehow
like your's, but I'm afraid, that is not the solution. My result:

  Z
  I
  P                                                           V   V   V
  C    V  V  V  V  V  V  V  V  V  V  V  V  V  V  V  V  V  V   1   2   3
  O    1  2  3  1  2  3  1  2  3  1  2  3  1  2  3  1  2  3   _   _   _
  D    _  _  _  _  _  _  _  _  _  _  _  _  _  _  _  _  _  _   1   1   1
  E    1  1  1  2  2  2  5  5  5  6  6  6  7  7  7  9  9  9   2   2   2

00001  1  1  1  2  2  2  .  .  .  .  .  .  .  .  .  .  .  .   .   .   .
00002  .  .  .  .  .  .  5  5  5  .  .  .  .  .  .  .  .  .   .   .   .
00003  .  .  .  .  .  .  .  .  .  6  6  6  7  7  7  9  9  9   .   .   .
00004  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  12  12  12


??
First thing I learned: these V-variables seem to be independant of
store_id. Then I saw, that the contents of the V-vars should always start
at V1_1 and seem to end all in (empty) V3_70

I don't know, where the store_id is used (?). From the example and the
desired output I can't see it.

For that I had no idea how to make this with transpose. Maybe some artists
can do it with that, or have a 2-line SQL solution, but I cannot. Not
because of my fingers, by the way, my problem is located a bit higher in
that case...
Gerhard




On Fri, 13 Apr 2007 06:51:39 -0400, Nat Wooding
<Nathaniel.Wooding@DOM.COM> wrote:

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