Date:         Thu, 8 Jun 2006 10:58:39 -0400
Reply-To:     "Richard A. DeVenezia" <rdevenezia@WILDBLUE.NET>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Richard A. DeVenezia" <rdevenezia@WILDBLUE.NET>
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/