Tobby,

I agree that the transformed data is a bad structure for many purposes. Perhaps I was a bit lax in not including the warning. Had I thought Bradly was working with a million cows or planning a database, I would have included it.

The requested transformation is common on SAS-L, and when it comes from someone who is not a programmer it deserves an answer. There must be many messages is the archives from myself, Sigurd, Howard, Paul, etc. explaining in detail why the transformed structure is a bad one for storing the data. However, the transformation preseves information, is reversible, and the reverse is just as easy to code.

Ian Whitlock -------------- Original message --------------

> Ian, > > In your opinion would it be better to leave the data structure the way it is > or transpose it to the form requested? Seems to me and i may be mistaken in > this that by transforming the data in this way it would make writing further > code to process the data harder. > > > > Toby Dunn > > > > > From: Ian Whitlock > Reply-To: iw1junk@COMCAST.NET > To: SAS-L@LISTSERV.UGA.EDU > Subject: Re: Help Formatting Data > Date: Tue, 5 Apr 2005 13:57:31 +0000 > > Bradley, > I see you were pointed to a nice package of macros for doing the > job. However, it is a classic problem, double transpose, that is > worth learning and understanding. Here is commented code for > your example. > data w ; input > Cow Testdate DIM milk pctf pctp mfreq ; > cards ; > 4546 20030927 30 82 2.4 3.2 3 > 4546 20031021 55 79 3.4 2.6 3 > 4546 20031121 85 75 3.5 2.7 3 > 4183 20031121 30 72 3.8 3.0 2 > ; > /* add sequence number to get unique record identifier */ > data w ; > set w ; > by cow notsorted ; /* cheat when data grouped by cow */ > /* may better to sort by cow and guarantee grouping */ > if first.cow then seq = 0 ; > seq + 1 ; > run ; > /* transpose to long and thin - one datum per record */ > proc transpose data = w out = t ; > by cow seq notsorted ; > vars dim--mfreq ; > run ; > /* construct new variable names > assume new names 32 bytes or less > */ > data t ( drop = seq ) ; > set t ; > _name_ = trim(_name_) || "_" || put ( seq, 2. -l ) ; > run ; > /* transpose to desired form - 1 record per cow */ > proc transpose data = t out = wanted ( drop = _name_ ) ; > by cow notsorted ; > run ; > > I dropped TESTDATE as requested. You may not want to lose this > piece of information. > > Ian Whitlock > ============ > Date: Mon, 4 Apr 2005 18:06:57 -0500 > Reply-To: "Bradley J. Heins" > Sender: "SAS(r) Discussion" > From: "Bradley J. Heins" > Subject: Help Formatting Data > Content-Type: text/plain; charset="us-ascii" > I am looking to format data that I have that is in row format and > am looking to put the data in one single record per cow. What I > need is a variable length record. > I am really not sure how to do this in SAS? > > Here is what I have. (Example) > Cow Testdate DIM milk pctf pctp mfreq > 4546 20030927 30 82 2.4 3.2 3 > 4546 20031021 55 79 3.4 2.6 3 > 4546 20031121 85 75 3.5 2.7 3 > 4183 20031121 30 72 3.8 3.0 2 > > This is what I would like the record to look like: > Cow dim milk pctf pctp mfreq dim milk pctf pctp > mfreq dim milk pctf pctp mfreg > 4546 30 82 2.4 3.2 3 55 79 3.4 2.6 > 3 85 75 3.5 2.7 3 > 4183 30 72 3.8 3.0 2 > Thanks. > >