BookmarkSubscribeRSS Feed
AbuYusuf
Calcite | Level 5

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
6 REPLIES 6
Reeza
Super User

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
Ammonite | Level 13
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
Super User
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
Calcite | Level 5

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
Calcite | Level 5

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
Super User

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. 

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 6 replies
  • 1690 views
  • 0 likes
  • 3 in conversation