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
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.