topic Re: count by date range in SAS Programming

count by date range

AbuYusuf — Fri, 20 Mar 2020 15:46:44 GMT

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

Re: count by date range

Reeza — Fri, 20 Mar 2020 15:55:42 GMT

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

Re: count by date range

AhmedAl_Attar — Fri, 20 Mar 2020 16:10:06 GMT

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;

Re: count by date range

Reeza — Fri, 20 Mar 2020 16:15:44 GMT

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.

Re: count by date range

AbuYusuf — Fri, 20 Mar 2020 19:11:20 GMT

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

Re: count by date range

AbuYusuf — Fri, 20 Mar 2020 19:58:04 GMT

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

Re: count by date range

Reeza — Sat, 21 Mar 2020 03:39:29 GMT

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.