## Create average daily population from data file

Solved
Occasional Contributor
Posts: 8

# Create average daily population from data file

I have a data file of individuals who were in a specific location over a year period (CY 2010).  The file contains an identifier, a start date, and a stop date.  Some individuals may have been there the entire time (e.g., start date is before 01jan2010 and stop date is after 31dec2010, or they were only there 3 days, or whatever).  Others may show up in this file multiple times if they came and went during that time.    I need to cycle through each day in the year and determine how many individuals were there on that day.  Then at the end, I should be able to summarize those numbers for each day, and divide by number of days, and get my average.

I'm attaching a sample file.  Subint is the individual identifier, and there's also strtdate and stopdate.

Accepted Solutions
Solution
‎03-14-2014 03:29 PM
Super User
Posts: 23,754

## Re: Create average daily population from data file

It is super flexible, just build the range into your where clause to select period of interest.

data part1;

set have;

where *Filter records for year of interest, ie 2010 via a macro variable*;

do date=start_date to end_date;

output;

end;

keep date var_interest;

run;

proc means data=part1;

class date;

var var_interest;

output out=summary1 mean(var_interest)=avg_day;

run;

All Replies
Super User
Posts: 23,754

## Re: Create average daily population from data file

How big is your actual file?

One quick way, in terms of programming is to expand your data set out to a record per day and then run a proc means.

If your data is too big this may not work well.

Occasional Contributor
Posts: 8

## Re: Create average daily population from data file

Ultimately I will need to do this over a 6 year period, and can anticipate having to do it somewhat frequently over changing periods of time, so I'm thinking I would like a macro, or something a little more flexible.

Solution
‎03-14-2014 03:29 PM
Super User
Posts: 23,754

## Re: Create average daily population from data file

It is super flexible, just build the range into your where clause to select period of interest.

data part1;

set have;

where *Filter records for year of interest, ie 2010 via a macro variable*;

do date=start_date to end_date;

output;

end;

keep date var_interest;

run;

proc means data=part1;

class date;

var var_interest;

output out=summary1 mean(var_interest)=avg_day;

run;

Occasional Contributor
Posts: 8

## Re: Create average daily population from data file

Thank you Reeza - I wouldn't have come up with that myself, but it works great, and did what I needed it too.  I'm attaching the code I ended up using to get an overall average daily population over CY 2010.  Very slick!  It will definitely come in handy.

Super User
Posts: 8,115

## Re: Create average daily population from data file

Another way to think about is to use an array for the days of the year.

proc sql noprint ;

select min(year(strtdate)),max(year(stopdate))

into :start,:stop

from dl.samplesasfile

;

quit;

data years ;

keep year mean days_in_yr ;

do year = &start to &stop ;

array daypop (366) ;

call missing( of daypop(*) );

do _n_=1 to nobs;

set dl.samplesasfile nobs=nobs point=_n_ ;

do i=max(mdy(1,1,year),strtdate) to min(mdy(12,31,year),stopdate) ;

day = i - mdy(1,1,year) + 1;

daypop(day) = sum(daypop(day), 1) ;

end;

end;

sum = sum(of daypop(*)) ;

days_in_yr = mdy(12,31,year) - mdy(1,1,year) + 1 ;

mean = sum / days_in_yr ;

put (year days_in_yr sum mean) (=) ;

output ;

end;

stop ;

run;

Occasional Contributor
Posts: 8

## Re: Create average daily population from data file

Thank you Tom - that was also something I would not have come up with on my own, but will be really helpful for the task where I need to calculate this over multiple years.  Very flexible also.  I really appreciate your help.

🔒 This topic is solved and locked.