BookmarkSubscribeRSS Feed
user24
Obsidian | Level 7

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           

 

 

4 REPLIES 4
Shmuel
Garnet | Level 18

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
Obsidian | Level 7

Thank you.

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

Shmuel
Garnet | Level 18

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
Diamond | Level 26 RW9
Diamond | Level 26

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1189 views
  • 0 likes
  • 3 in conversation