topic Counting vertically problem in SAS Programming

Counting vertically problem

hhchenfx — Sun, 20 Oct 2013 03:01:05 GMT

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

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

Re: Counting vertically problem

Reeza — Sun, 20 Oct 2013 03:40:16 GMT

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;

Re: Counting vertically problem

Haikuo — Sun, 20 Oct 2013 04:10:10 GMT

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;

keep time count;

run;

Re: Counting vertically problem

Tom — Sun, 20 Oct 2013 16:11:44 GMT

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;

Re: Counting vertically problem

hhchenfx — Sun, 20 Oct 2013 16:20:30 GMT

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;

Re: Counting vertically problem

Tom — Sun, 20 Oct 2013 17:01:47 GMT

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;

Re: Counting vertically problem

hhchenfx — Sun, 20 Oct 2013 18:58:34 GMT

Thank you, Tom.

Yes, it is exactly what I want to do.

Still a lot to learn for me.

HHC