Howard,

John Doe probrably did not give us a representative data set but this should work for th etwo problems you showed;

data one ; infile cards ; input week $ name $ A $ B $ Value ; cards ; 02 shoes a2 b2 120 01 shoes a2 b2 80 ; run ;

proc sort data = one ; by Week A B ; run ;

proc transpose data = one out = two (drop = _name_) prefix = Week_ let ; by name A B notsorted; var Value ; id week ; run ;

proc print data = two ; run ;

On the other hand we could just as easily keep if'ing this thing till there is no possible solution.

Toby Dunn

From: "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM> Reply-To: "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM> To: SAS-L@LISTSERV.UGA.EDU Subject: Re: How to create columns from obersation values Date: Thu, 29 Sep 2005 17:58:57 -0400

John Doe did not present enough data to provide a good test. The data lines he did provide were (after a bit of cleanup)

02 shooes a1 b1 120 01 shooes a2 b2 80

When I plug them into Toby's code, I get

ERROR: Data set WORK.ONE is not sorted in ascending sequence.

That's easily fixed with a NOTSORTED option on the BY statement.

Then I add more data:

01 socks a2 b2 99 02 socks a2 b2 88

and when I run the code I get

ERROR: The ID value "Week_01" occurs twice in the same BY group.

This makes me reconsider things. I think that what J.D. wants is:

proc transpose data = one out = two (drop = _name_) prefix = Week_ ; by name A B notsorted; var Value ; id week ; run ;

Result:

Obs name A B Week_02 Week_01

1 shooes a1 b1 120 . 2 shooes a2 b2 . 80 3 socks a2 b2 88 99

I don't think I've ever found a use for the COPY statement.

On Wed, 28 Sep 2005 14:37:25 -0700, Toby <tobydunn@HOTMAIL.COM> wrote:

>John Doe, > >Not too sure why you want yoru data this way but hey to each their own. > >data one ; >infile cards ; >input week $ name $ A $ B $ Value ; >cards ; >01 shoes a1 b1 120 >02 shoes a2 b2 80 >; >run ; > >proc transpose > data = one > out = two (drop = _name_) prefix = Week_ ; >by week ; >var Value ; >id week ; >copy name A B ; >run ; > >proc print > data = two ; >run ; > >Toby Dunn > > >Hello, > > >i would like to break down some values in columns. Let's look the >picture: > > >What i have: >--------------------------------- >| week | name | a | b | value | >--------------------------------- >| 02 | shooes | a1| b1| 120 $ | >| 01 | ... | a2| b2| 80 $ | > > >What i want: >---------------------------------------- >| name | a | b | week_01 | week_02 | ... >----------------------------------------- >| shooes | a1| b1| . | 120 $ | ... >| ... | a2| b2| 80 $ | . | ... > > >* i don't know how many week there is (this will change with time, >because in the real data, the year is included) >* off course, the week values are not unique > > >I've tried >- transpose : without success >- array : you need to know the name of the new column in advance > > >Any help really appreciated