Counting vertically problem

[hhchenfx]

Hello Everyone,

I have a timeline dataset (simple 1,2,3,4..) and a dataset that record time a give person enter and exit a room.

I want to know at a given time, how many people are in the room.

I can do it with Proc sql but I am trying to count it vertically and still cannot get it.

My vertical code is as below and you can see, the method is quite clear.

For a give point in time, IF enter<time <exit then do; n=n+1;

Thank you for your help.

HHC

data time;
input time;
datalines;
1
2
3
4
5
6
7
8

9

;
data have;
input enter exit;
datalines;
1 4
1 6
1 8
2 6
3 7
4 5
4 7
;

data Number;
set time;

n=0;
i+1;
set have;
if enter<time and time<exit then do;
n=n+1;
end;
run;

[Reeza]

Here's a somewhat inefficient solution:

data have;

input enter exit;

datalines;

1 4

1 6

1 8

2 6

3 7

4 5

4 7

;

run;

data have2;

set have;

do person=enter to exit;

  output;

end;

run;

proc freq data=have2;

table person/out=want1;

run;

[Haikuo]

Hash approach,

data want;

  if _n_=1 then do;

    if 0 then set have;

  declare hash h(dataset:'have', multidata:'y');

  h.definekey('enter');

  h.definedata(all:'y');

  h.definedone();

end;

set time;

count=0;

exit=0;

   do i=time-1 by -1 while (i>0);

     rc=h.find(key:i);

  do rc=0 by 0 while (rc=0);

    if time<exit then count+1;

    rc=h.find_next(key:i);

  end;

   end;

  keep time count;

run;

[Tom]

The number of people in a room at a particular discrete points in time can easily be modeled as an array.

data have;

  person+1;

  input enter exit @@;

cards;

1 4 1 6 1 8 2 6 3 7 4 5 4 7

;

data want ;

  array cnt (10) _temporary_ ;

  if eof then do time=1 to dim(cnt);

    count=sum(0,cnt(time));

    output;

    keep time count;

  end;

  set have end=eof;

  do time=enter to exit ;

    cnt(time) = sum(1,cnt(time));

  end;

run;

proc print;

run;

[hhchenfx]

Thank you all for different suggestion.

Since I try to practice the approach below (which I learn from Tom and other), I really appreciate if you could debug that code.

Have a nice weekend.

HHC

data Number;
set time;

n=0;
i+1;
set have;
if enter<time and time<exit then do;
n=n+1;
end;
run;

[Tom · Accepted solution]

I think what you might be trying to do is loop over the HAVE dataset for every record in the TIME dataset.

data number;

  set time;

  n=0;

  do i=1 to nobs ;

    set have point=i nobs=nobs;

    n=n+ (enter <= time <= exit) ;

  end;

  keep time n ;

run;

[hhchenfx]

Thank you, Tom.

Yes, it is exactly what I want to do.

Still a lot to learn for me.

HHC