Create average daily population from data file

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

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.


Attachment

Accepted Solutions
Solution
‎03-14-2014 03:29 PM
Super User
Posts: 17,831

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;

View solution in original post


All Replies
Super User
Posts: 17,831

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: 17,831

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.

Attachment
Super User
Super User
Posts: 6,500

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 670 views
  • 3 likes
  • 3 in conversation