LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (April 2007, week 3)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
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;


Back to: Top of message | Previous page | Main SAS-L page