fill empty cells between non missing cells

[panda]

Hi - 

I want to fill the missing data between non missing data, the variales are hours, people can work during day time or night time:

id   0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

1                            1                                        1      

2                                                  1                                     1

3                      1                                                                            1

Now I know the start and stop hour for each id, for instance, id1 started at 8am and stopped at 5pm, id2 started at 1pm and stopped 

at 9pm. id3 worked at night so he/she started at 11pm and stopped next morning at 6am.  I want to fill the hours between their start

and stop hours so that the output can look like this (the time when people were not at work should be left as missing):

id   0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

1                            1 1 1   1   1   1   1   1    1  1                  

2                                                  1   1   1   1   1  1    1   1   1

3    1 1 1 1 1 1                                                                              1

Any idea about how to do that? I think the tricky part is for people who were on nightshift (crossed midnight).

Thanks!!!

[art297]

What does your data really look like? Surely you don't have variables like 0, 1, 2, etc. since, unless you're using validvarname=any, SAS variables can't start with a number.

Art, CEO, AnalystFinder.com

[panda]

Sorry, the variables are like time0 time1 time2 ... time23.

[art297 · Accepted solution]

Here is one way:

data have;
  input id time0-time23;
  cards;
1 . . . . . . . . 1 . . . . . . . . 1 . . . . . .                 
2 . . . . . . . . . . . . . 1 . . . . . . . 1 . .
3 . . . . . . 1 . . . . . . . . . . . . . . . . 1
4 . . . . . . 1 . . . . . . . . . . . . . . . 1 .
5 1 . . . . . . . . . . . . . . . . . . . . . . 1
;

data want (drop=x y i);
  set have;
  array times(0:23) time0-time23;
  x=whichn(1,of times(*))-1;
  y=24-whichn(1,of time23-time0);
  if y-x le 12 then do i=x+1 to y;
    times(i)=1;
  end;
  else do;
    do i=y to 23;
      times(i)=1;
    end;
    do i=0 to x;
      times(i)=1;
    end;
  end;
run;

Art, CEO, AnalystFinder.com

[panda]

Thanks!!!

I am not familiar with the whichn function, cannot find sas profile on this function. Could you explain briefly what does it do or do you

have any links about it?

[art297]

Here is a link to the documentation: http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a003122573.htm

When you enter the variable list backwards (like time23-time0) is searches backwards.

Art, CEO, AnalystFinder.com

[art297]

Better yet, here is a link to the current documentation: http://support.sas.com/documentation/cdl/en/lefunctionsref/69762/HTML/default/viewer.htm#p0zs0pv38me...

If you save that link, you'll likely find it useful as the left most column lists all of the SAS functions and call routines and has links to their documentation.

Art, CEO, AnalystFinder.com

[panda]

Sorry I forgot another scenario of the problem, if the data is like this:

data have;
  input id time0-time23;
  cards;
1 . . . . 1 . . . 1 . . . . . 1 . . . 1 . . . . .                 
2 . 1 . . 1 . . . . . . . . 1 . . . . . . . 1 . .
3 . . . . . . 1 . . 1 . . . . . . . . 1 . . . . 1
4 . . 1 . . . 1 . . . . . . . . . 1 . . . . . 1 .
5 . . . . 1 . . . . . . . . . . . . . . 1 . . . . 
;

In this case, each person has multiple start and stop hours, like for id 1, he/she started at 4am and stopped at 8am, then started again

at 2pm and stopped at 6pm, want to fill the gap between each start and stop times but leave missing cells as missing between them:

1 . . . . 1 1 1 1 1 . . . . . 1 1 1 1 1 . . . . .                 
2 . 1 1 1 1 . . . . . . . . 1 1 1 1 1 1 1 1 1 . .
3 . . . . . . 1 1 1 1 . . . . . . . . 1 1 1 1 1 1
4 1 1 1 . . . 1 1 1 1 1 1 1 1 1 1 1 . . . . . 1 1
5 1 1 1 1 1 . . . . . . . . . . . . . . 1 1 1 1 1 

 Thanks!!!

[art297]

@panda: I'm too tired to think of a fancy way to to that so here is a brute force method. The only scenario it can't account for is a series of multiple shifts combined with an overnight shift:

data have;
  input id time0-time23;
  cards;
0 1 1 . . . . . . 1 . 1 . . . . . . . 1 1 . 1 . 1                 
1 . . . . 1 . . . 1 . . . . . 1 . . . 1 . . . . .                 
2 . 1 . . 1 . . . . . . . . 1 . . . . . . . 1 . .
3 . . . . . . 1 . . 1 . . . . . . . . 1 . . . . 1
4 . . 1 . . . 1 . . . . . . . . . 1 . . . . . 1 .
5 . . . . 1 . . . . . . . . . . . . . . 1 . . . . 
;

data want (drop=x y i /*counter*/);
  set have;
  array times(0:23) time0-time23;
  length times_string $24;
  times_string='';
  counter=0;
  do i=0 to 23;
    if times(i) eq 1 then do;
      counter+1;
      times_string=catt(times_string,mod(counter,2));
    end;
    else times_string=catt(times_string,'.');
  end;
  if counter eq 2 then do;
    x=whichn(1,of times(*))-1;
    y=24-whichn(1,of time23-time0);
    if y-x le 12 then do i=x+1 to y;
      times(i)=1;
    end;
    else do;
      do i=y to 23;
        times(i)=1;
      end;
      do i=0 to x;
        times(i)=1;
      end;
    end;
  end;
  else if counter gt 2 then do;
    do x=0 to 22;
      if times(x) eq 1 then do y=x+1 to 23;
        if times(y) eq 1 then do;
          x=y;
          leave;
        end;
        else times(y)=1;
      end;
    end;
  end;
run;

Art, CEO, AnalystFinder.com

[panda]

Thanks so much, this is very helpful!!!