SAS: do loop help: variable =1 from start of by group until marker =1

Question

In SAS, I have data that is sorted by time. For the first n minutes, a marker variable is marked 0 then changes to 1. This occurs within a 'by' variable. I want to create a new variable that =1 for each minute from the start of the 'by' group observations until the marker variable gets to 1. I have tried dozens of do loop combinations and have been unsuccessful. Any help would be appreciated!

Can you provide an example data set... what you want it to look like in the end? — Mark Nielsen, Jun 18 '14 at 16:11
Example data is necessary for this question to be a good question, for sure. — Joe, Jun 18 '14 at 18:18

Mark Nielsen · Answer 1 · 2014-06-18T16:28:41.277

From a data set like this:

by_var marker
A      0
A      0
A      1
B      0
B      0
B      1

You can get a data set that looks like this (if each observation is a minute):

by_var marker minute
A      0      1
A      0      2
A      1      3
B      0      1
B      0      2
B      1      3

From your data set by sorting by by_var and using a simple data step:

data my_data2;
  set my_data;
  by by_var;
  if first.by_var then minute = 0;
  minute+1;
  output;
run;

Or did you need to change something like this (with a time variable):

by_var marker time
A      0      12:34:01
A      0      12:34:59
A      0      12:35:01
A      0      12:36:12
A      1      12:36:50
B      0      12:34:01
B      0      12:34:09
B      0      12:34:59
B      0      12:36:12
B      1      12:37:50

To this:

by_var    marker      time         time2    minutes    Cumsum_minutes

  A          0      12:34:01           .       0           0
  A          0      12:34:59    12:34:01       0           0
  A          0      12:35:01    12:34:59       1           1
  A          0      12:36:12    12:35:01       1           2
  A          1      12:36:50    12:36:12       0           2
  B          0      12:34:01    12:36:50       0           0
  B          0      12:34:09    12:34:01       0           0
  B          0      12:34:59    12:34:09       0           0
  B          0      12:36:12    12:34:59       2           2
  B          1      12:37:50    12:36:12       1           3

CODE:

data my_data;
  input by_var $ marker time time.;
  format time time.;
  cards;
  A 0 12:34:01
  A 0 12:34:59
  A 0 12:35:01
  A 0 12:36:12
  A 1 12:36:50
  B 0 12:34:01
  B 0 12:34:09
  B 0 12:34:59
  B 0 12:36:12
  B 1 12:37:50
  ;
run;

proc sort data=my_data;by by_var;run;

data final;
  set my_data;
  format time2 time.;
  by by_var;
  time2=lag(time);
  if first.by_var then do;
    minutes = 0;
    Cumsum_minutes = 0;
  end;
  else minutes=intck("minutes",time2,time);
  Cumsum_minutes+minutes;
run;

SAS: do loop help: variable =1 from start of by group until marker =1

1 Answers1