Date: Wed, 1 Feb 2006 00:11:05 -0500
Reply-To: sashole@bellsouth.net
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: "Paul M. Dorfman" <sashole@BELLSOUTH.NET>
Organization: Sashole of Florida
Subject: Re: How to put obs that are in different rows into one row
In-Reply-To: <drol6e$41n$1@news.Stanford.EDU>
Content-Type: text/plain; charset="us-ascii"
Toby,
"Huge", you say? I usually take such claims with a bit of incredulity. Suppose:
451 %let m = 31 ;
452 %let o = 10001 ;
453 %let s = 50 ; *~appr;
454
455 data h ;
456 do oid = 1 to &o ;
457 do sid = 1 to ceil (ranuni (1) * 2 * &s) ;
458 array m [&m] ;
459 do _n_ = 1 to &m ;
460 m [_n_] = oid + sid ;
461 end ;
462 output ;
463 end ;
464 end ;
465 run ;
NOTE: The data set WORK.H has 503884 observations and 33 variables.
NOTE: DATA statement used (Total process time):
real time 4.50 seconds
cpu time 3.06 seconds
Big enough ;)? First, we need a preliminary pass to get the maximal SID:
467 proc sql noprint ;
468 select put (max (sid), best.-l) into :maxsid from a ;
469 quit ;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.20 seconds
cpu time 0.20 seconds
Next, a way is needed to form variable names in the mold of m1_1,...,m101_31
before the transposing step - the DATA step compiler is not so nice as to do it
for you if you merely throw m1_1-m101_31 list specification at it. Under the
circumstances, this work seems to be best cut for a macro:
471 %macro aux (pfx, dim1, dim2) ;
472 %local i j ;
473 %do i = 1 %to &dim1 ;
474 %do j = 1 %to &dim2 ;
475 &pfx&i._&j
476 %end ;
477 %end ;
478 %mend aux ;
The rest is simple (presuming, of course, that H is duly ordered by oid (here
the order is intrinsic):
480 data v (drop = sid m1 - m&m) ;
481 do until (last.oid) ;
482 set h ;
483 by oid ;
484 array m_1d [*] m: ;
485 array m_2d [&maxsid, &m] %aux (m, &maxsid, &m) ;
486 do _n_ = 1 to &m ;
487 m_2d [sid, _n_] = m_1d [_n_] ;
488 end ;
489 end ;
490 run ;
NOTE: There were 503884 observations read from the data set WORK.H.
NOTE: The data set WORK.V has 10001 observations and 3132 variables.
NOTE: DATA statement used (Total process time):
real time 9.26 seconds
cpu time 8.78 seconds
I realize that 10 seconds can put a real strain on one's patience, but note that
this was run on a 2*1GHz*1Gb desktop (V9.1.3). I bet your more contemporary
setup will beat my antiquity handily.
Kind regards
------------
Paul Dorfman
Jax, FL
------------
> -----Original Message-----
> From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On
> Behalf Of toby989@HOTPOP.COM
> Sent: Tuesday, January 31, 2006 4:35 PM
> To: SAS-L@LISTSERV.UGA.EDU
> Subject: How to put obs that are in different rows into one row
>
> Hi All
>
> My data looks like:
>
> organizatonid subjectid m1 m2 m3 m4 m5
> 2 1 55 66 77 44 55
> 2 2 54 64 22 65 26
> 4 3 56 77 34 73 84
> 4 4 54 23 54 11 65
> 5 5 34 73 26 73 74
> 5 6 34 55 23 55 45
> 5 7 34 36 34 24 74
>
> And I want
> oid m11 m12 m13 m14 m15 m21 m22 m23 m24 m25 m31 m32 m33 m34 m35
> 2 55 66 77 44 55 54 64 22 65 26
> 4 56 77 34 73 84 54 23 54 11 65
> 5 34 73 26 73 74 34 55 23 55 45 34 36 34 24 74
>
> The dataset is huge. There are more than 5 measurements
> illustrated above, there are more than 3 subjects per
> organization, and there are more than these 3 organizations.
>
> I was thinking of using set or merge in a datastep together with a do
> (it.) statement but then I cant access the counter to create
> variable names. I am also not expereinced enought to know how
> to access specific rows with their obs number.
>
> Thanks for your hint.
>
> Toby
>
