Total for same observations

[user24]

I have table as below: For same id

id    class     time     OV

3       F           3      

4       F           1          OV

4       F          3          OV

5       M         1          OV

6       F          1

I need to make the table as below; for the same id sum the time and make OV1 and OV2

id        class      time        OV

3          F           3

4          F           4            OV1

4          F           4            OV2

5          M          1             OV

6          F           1           

[Shmuel]

steps:

1) sum the time per ID

2) update origin by totals per id

proc summary data=have missing nway;

      class ID;

      var time;

      output out=totals(drop=_time_  _freq_) sum=;

run;

data want;

  update have totals;

   by ID;

        retian count;

       if first.id then count=0;

        if not (first.ID and last.ID) then do;

           count+1;

          ov = 'OV' || left(count);

       end; 

run;

[user24]

Thank you.

It is close but not correct. For the OV2, total is not right.

[Shmuel]

You are right. here is a tested code:

data have;
  INFILE DATALINES TRUNCOVER;
  input id class $ time ov $;
datalines;
3 F 3
4 F 1 OV
4 F 3 OV
5 M 1 OV
6 F 1
; RUN;
proc summary data=have missing nway;
  class id;
  var time;
  output out=totals(drop=_freq_ _type_) sum=;
run;

data want(drop=count);
merge have(drop=time) 
      totals;
 by id;
    retain count time;
    if first.id then count=0;
    if not(first.id and last.id) then do;
       count+1;
       ov = 'OV' || left(count);
    end;
run;

[RW9]

Hi,

Good idea to post test data as as datastep.  What makes OV1 and OV2?  As the output:

4          F           4            OV1

4          F           4            OV2

There is no discernation?  Why not have:

ID        CLASS    TIME_SUM      MAX_OV

4          F           4                    2

Achieves the same as previous, but with one obs less.  Any further procedure:

where max_ov <= 1

or 

where max_ov <= 2

To get your two groups.