count by date range

[AbuYusuf]

Hi,

I'd like to calculate the number of people staying at this location by date. I have a unique ID, entrance date and exit date. I know this question has been answered using sql, but I was wondering if there was a way to do it without sql. I don't know sql and right now I am a bit pressed for time to try to figure it out. My data are like this:

Thank you very much for your help!

ID	Entrance	Exit
1	27-Mar-2018	05-Apr-2018
2	26-Mar-2018	01-Apr-2018
3	02-Mar-2018	20-Apr-2018
4	26-Mar-2018	30-Apr-2018
5	26-Mar-2018	05-Apr-2018
6	03-Apr-2018	12-Apr-2018
7	19-Jun-2017	18-Apr-2018
8	10-Apr-2018	11-Apr-2018
9	14-Apr-2018	16-Apr-2018
10	06-Apr-2018	08-Apr-2018
11	26-Feb-2018	06-Apr-2018
12	31-Jan-2018	23-Apr-2018
13	29-Mar-2018	12-Apr-2018
14	05-Apr-2018	30-Apr-2018
15	28-Mar-2018	03-Apr-2018
16	19-Apr-2018	20-Apr-2018
17	18-Apr-2018	20-Apr-2018
18	16-Apr-2018	19-Apr-2018
19	05-Apr-2018	06-Apr-2018
20	04-Apr-2018	07-Apr-2018
21	05-Apr-2018	10-Apr-2018
22	23-Apr-2018	24-Apr-2018
23	01-Apr-2018	06-Apr-2018
24	24-Apr-2018	30-Apr-2018
25	06-Apr-2018	11-Apr-2018
26	11-Apr-2018	14-Apr-2018
27	12-Apr-2018	16-Apr-2018
28	16-Apr-2018	17-Apr-2018
29	12-Apr-2018	18-Apr-2018
30	28-Mar-2018	03-Apr-2018

[Reeza]

Create a record for every day and proc freq is the brute force approach. Hopefully your data set is small enough to do this.

data long / view=long;
set have;

do date=entrance to exit;
output;
end;

run;

proc freq data=long NOPRINT;
table ID*Date / out = CountByDateID;
run;

---

@AbuYusuf wrote:

Hi,

I'd like to calculate the number of people staying at this location by date. I have a unique ID, entrance date and exit date. I know this question has been answered using sql, but I was wondering if there was a way to do it without sql. I don't know sql and right now I am a bit pressed for time to try to figure it out. My data are like this:

Thank you very much for your help!

ID	Entrance	Exit
1	27-Mar-2018	05-Apr-2018
2	26-Mar-2018	01-Apr-2018
3	02-Mar-2018	20-Apr-2018
4	26-Mar-2018	30-Apr-2018
5	26-Mar-2018	05-Apr-2018
6	03-Apr-2018	12-Apr-2018
7	19-Jun-2017	18-Apr-2018
8	10-Apr-2018	11-Apr-2018
9	14-Apr-2018	16-Apr-2018
10	06-Apr-2018	08-Apr-2018
11	26-Feb-2018	06-Apr-2018
12	31-Jan-2018	23-Apr-2018
13	29-Mar-2018	12-Apr-2018
14	05-Apr-2018	30-Apr-2018
15	28-Mar-2018	03-Apr-2018
16	19-Apr-2018	20-Apr-2018
17	18-Apr-2018	20-Apr-2018
18	16-Apr-2018	19-Apr-2018
19	05-Apr-2018	06-Apr-2018
20	04-Apr-2018	07-Apr-2018
21	05-Apr-2018	10-Apr-2018
22	23-Apr-2018	24-Apr-2018
23	01-Apr-2018	06-Apr-2018
24	24-Apr-2018	30-Apr-2018
25	06-Apr-2018	11-Apr-2018
26	11-Apr-2018	14-Apr-2018
27	12-Apr-2018	16-Apr-2018
28	16-Apr-2018	17-Apr-2018
29	12-Apr-2018	18-Apr-2018
30	28-Mar-2018	03-Apr-2018

---

[AhmedAl_Attar]

Adding the following Proc step, would provide a Date based count

proc summary data=long nway;
class date;
output out=CountByDate(DROP=_TYPE_ RENAME=(_FREQ_=count));
run;

[Reeza]

That could replace the PROC FREQ, but counts dates, not necessarily by location which the OP requested. I assumed ID could be location but re-reading it isn't clear which is the location variable. OP can clarify and remove ID if needed from PROC FREQ.

[AbuYusuf]

Hi,

Thank you very much for your help! I am getting an error when I run the code:

ERROR: The requested type of view (Input or Output) cannot be determined. 

I haven't seen such error before... Would you know what to do about it?

thank you

[AbuYusuf]

Hi,

That was my mistake - the code runs, thank you very much! Now I am just trying to figure out if I am getting what I need. Would you mind letting me know what this part of the code does?

do date=ALCadm to discharge_date;
output;
end;

thank you

[Reeza]

It creates a line for every date in the range. So if your date range spans 40 days , it creates 40 lines. However, it’s a view which means it only does this when you try to view it or use the data set.