fill empty cells between non missing cells

Accepted Solution Solved
Reply
Contributor
Posts: 28
Accepted Solution

fill empty cells between non missing cells

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


Accepted Solutions
Solution
2 weeks ago
Esteemed Advisor
Posts: 7,295

Re: fill empty cells between non missing cells

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

 

View solution in original post


All Replies
Esteemed Advisor
Posts: 7,295

Re: fill empty cells between non missing cells

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

 

Contributor
Posts: 28

Re: fill empty cells between non missing cells

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

Solution
2 weeks ago
Esteemed Advisor
Posts: 7,295

Re: fill empty cells between non missing cells

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

 

Contributor
Posts: 28

Re: fill empty cells between non missing cells

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?

 

 

Esteemed Advisor
Posts: 7,295

Re: fill empty cells between non missing cells

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

 

Esteemed Advisor
Posts: 7,295

Re: fill empty cells between non missing cells

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

 

Contributor
Posts: 28

Re: fill empty cells between non missing cells

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

Esteemed Advisor
Posts: 7,295

Re: fill empty cells between non missing cells

@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

Contributor
Posts: 28

Re: fill empty cells between non missing cells

Thanks so much, this is very helpful!!!

 

 

 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 138 views
  • 4 likes
  • 2 in conversation