topic Re: Total for same observations in SAS Programming

Total for same observations

user24 — Tue, 31 Jan 2017 13:43:27 GMT

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

Re: Total for same observations

Shmuel — Tue, 31 Jan 2017 14:00:56 GMT

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;

Re: Total for same observations

RW9 — Tue, 31 Jan 2017 14:03:32 GMT

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

where max_ov <= 2

To get your two groups.

Re: Total for same observations

user24 — Tue, 31 Jan 2017 14:11:59 GMT

Thank you.

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

Re: Total for same observations

Shmuel — Tue, 31 Jan 2017 14:38:18 GMT

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;