```Date: Thu, 8 Jun 2006 10:58:39 -0400 Reply-To: "Richard A. DeVenezia" Sender: "SAS(r) Discussion" From: "Richard A. DeVenezia" Subject: Re: A tricky data manipulation (long) Comments: To: sas-l@uga.edu "Huang, Ya" wrote: snip What field of analysis requires such machinations on the data? > As you can see, each blknum have multiple records, with nr starts > from 1 and > incremental. nr can be duplicate. Minimum number of records for each > blknum is 1. > Now here is what I need: > > First I need to sort by blknum and original nr. Then I need to add > at the beginning of the block one extra record, and set a var flag=1. > Then I need to reset the nr so that it starts from 1 and incremental > all the way > to the end of the data. Consecutive nr will get the same new nr > number, newly added record get the smallest nr number in that block: This is a little unclear, but your sample indicates each new row created with flag=1 gets it own nr+1 bump -- not quite the same as "newly added record get the smallest nr number in that block" -- unless that means the new record is always assigned the lowest nr in a block. In the sample code there are some boundary situations (E & F), blocks that start with only one nr get turned into a block with two nrs (due to the new record specification). Also, the case for a later block of 6 nrs having flag=1 at the start of the group has been added (see index=4). Use a standard data splitting technique on 'foo4' as discussed in numerous previous threads, or simply process the data BY the new [index] variable. ------------------------------- data foo; input nr blklabel \$ blknum; cards; 1 A 1 1 A 1 2 A 1 3 A 1 3 A 1 1 C 3 2 C 3 2 C 3 3 C 3 4 C 3 5 C 3 5 C 3 5 C 3 1 D 2 2 D 2 2 D 2 3 D 2 1 E 4 1 F 5 1 H 7 1 H 7 2 H 7 3 H 7 3 H 7 1 G 6 1 G 6 2 G 6 3 G 6 4 G 6 5 G 6 ; proc sort data=foo out=foo2; by blknum; run; data foo3; retain num 0; set foo2; by blknum; if first.blknum then do; num + 1; flag = 1; OUTPUT; flag = .; end; num + (nr ne lag(nr) OR first.blknum); OUTPUT; format _numeric_ 5.; run; data foo4;*(keep=index numr blknum blklabel flag); index + 1; retain numr; do seq = 0 by 0 until (seq=6 and last.num); set foo3; by num; if seq = 0 then if flag = . then do; seq + 1; flag = 1; numr = 1; OUTPUT; flag = .; end; else numr = num-1; seq + first.num; numr + first.num; OUTPUT; end; format _numeric_ 5.; run; ------------------------------- Richard A. DeVenezia http://www.devenezia.com/ ```

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