topic Re: fill empty cells between non missing cells in SAS Programming

fill empty cells between non missing cells

panda — Sun, 09 Jul 2017 17:52:08 GMT

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!!!

Re: fill empty cells between non missing cells

art297 — Sun, 09 Jul 2017 18:20:31 GMT

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

Re: fill empty cells between non missing cells

panda — Sun, 09 Jul 2017 18:21:36 GMT

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

Re: fill empty cells between non missing cells

art297 — Sun, 09 Jul 2017 19:03:57 GMT

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

Re: fill empty cells between non missing cells

panda — Sun, 09 Jul 2017 19:14:42 GMT

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?

Re: fill empty cells between non missing cells

art297 — Sun, 09 Jul 2017 19:24:23 GMT

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

Re: fill empty cells between non missing cells

art297 — Sun, 09 Jul 2017 19:28:11 GMT

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

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

Re: fill empty cells between non missing cells

panda — Sun, 09 Jul 2017 19:53:09 GMT

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!!!

Re: fill empty cells between non missing cells

art297 — Mon, 10 Jul 2017 02:38:50 GMT

@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

Re: fill empty cells between non missing cells

panda — Mon, 10 Jul 2017 13:04:11 GMT

Thanks so much, this is very helpful!!!