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;
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;
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;
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;
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;
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;
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;
Thank you, Tom.
Yes, it is exactly what I want to do.
Still a lot to learn for me.
HHC
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.