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
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;
Thank you.
It is close but not correct. For the OV2, total is not right.
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;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.