Date:         Fri, 30 Sep 2005 06:55:16 -0700
Reply-To:     Toby <tobydunn@HOTMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Toby <tobydunn@HOTMAIL.COM>
Organization: http://groups.google.com
Subject:      Re: Help: collapse records to achieve minimum record size
Comments: To: sas-l@uga.edu
In-Reply-To:  <d8c3c7b47bfba1c4848667eb20213fae.43464@mygate.mailgate.org>
Content-Type: text/plain; charset="iso-8859-1"

SJ ,

data one ;
infile cards ;
input group $ xx1 xx2 xx3 ;
cards ;
     000       631       1220       511
     001      3582       1077        2
     002       928       1700        9
     003       785        511         5
     004       875        744         8
     005       649        676        16
     006       752        929        15
     007       781       1094        21
     008       771       1381        53
     009       711       1624        87
     010       841       2027       144
     011      1083       5408       610
     012      1763       8093     15526
     013      3023      12368     22592
;
run ;


data two (drop = _: group) ;
length NewGroup $50. ;
set one ;
retain NewGroup ' ' ;

if _xx3 < 20 then do ;
  _xx1 + xx1 ;
  _xx2 + xx2 ;
  _xx3 + xx3 ;
  NewGroup = compbl(Group||NewGroup) ;
end ;

if _xx3 >= 20 then do ;
  xx1 = _xx1 ;
  xx2 = _xx2 ;
  xx3 = _xx3 ;
  output ;
  _xx1 = 0 ;
  _xx2 = 0 ;
  _xx3 = 0 ;
  NewGroup = ' ' ;
end ;

run ;

proc print ;
run ;


Toby Dunn

Hello all;


I have the data below consisting of variables: group, xx1 xx2, xx3.


 group      xx1       xx2       xx3


     000       631       1220       511
     001      3582       1077        2
     002       928       1700        9
     003       785        511         5
     004       875        744         8
     005       649        676        16
     006       752        929        15
     007       781       1094        21
     008       771       1381        53
     009       711       1624        87
     010       841       2027       144
     011      1083       5408       610
     012      1763       8093     15526
     013      3023      12368     22592


For each of the xx's, my goal is to have at least 20 units in the
group.
If there is not enough, then adjacent groups will be combined until at
least the total counts
is 20. (Please note that if groups in xx3 are combined, the
corresponding groups in
xx1 and xx2 must also be combined) Once this is achieved, I would like
to record in a separate column,
which groups were combined to achieve the result.


For example, groups 001, 002,003, 004 should be combined to achieve a
count of at least 20 (2+9+5+8 = 24) in xx3.


The final datshould look like:


xx1    xx2      xx3   newgroup
631    1220     511    000
6170   4032      24    001,002,003,004
1401   1605      31    005,006
 781   1094      21    007
 771   1381      53    008
 711   1624      87    009
 841   2027     144    010
1083   5408     610    011
1763   8093   15526    012
3023  12368   22592    013


I would appreciate all help with regards to approaches to coding.


Thanks,


SJ.