Person Count for several years

[Dynamike]

How can i simplify this code (macro or loop):

Proc SQL;
Create Table Test as select distinct
Count(distinct case when 2005 GE year(start) and 2005 LE year(end) then person else . end) as person_count_2005
,
Count(distinct case when 2006 GE year(start) and 2006 LE year(end) then person else . end) as person_count_2006
,
Count(distinct case when 2007 GE year(start) and 2007 LE year(end) then person else . end) as person_count_2007
,
Count(distinct case when 2008 GE year(start) and 2008 LE year(end) then person else . end) as person_count_2008
,
Count(distinct case when 2009 GE year(start) and 2009 LE year(end) then person else . end) as person_count_2009
,
Count(distinct case when 2010GE year(start) and 2010 LE year(end) then person else . end) as person_count_2010
,
Count(distinct case when 2011 GE year(start) and 2011 LE year(end) then person else . end) as person_count_2011
from data;
quit;

This is example only for the years 2005 to 2011, but i want count person for more years.

Thank you for your effort!

Dynamike

[VDD]

does this code work and give you the expected results for each person and year how you currently have it?

[Dynamike]

Thank you for your answer.

The code does not work properly

See the reply to Reeza for more Information, what the result should look like.

[Reeza]

If you show your input data and expected output you can likely get a better answer. Yes, there are ways to simplify that code.

---

@Dynamike wrote:

How can i simplify this code (macro or loop):

Proc SQL;
Create Table Test as select distinct
Count(distinct case when 2005 GE year(start) and 2005 LE year(end) then person else . end) as person_count_2005
,
Count(distinct case when 2006 GE year(start) and 2006 LE year(end) then person else . end) as person_count_2006
,
Count(distinct case when 2007 GE year(start) and 2007 LE year(end) then person else . end) as person_count_2007
,
Count(distinct case when 2008 GE year(start) and 2008 LE year(end) then person else . end) as person_count_2008
,
Count(distinct case when 2009 GE year(start) and 2009 LE year(end) then person else . end) as person_count_2009
,
Count(distinct case when 2010GE year(start) and 2010 LE year(end) then person else . end) as person_count_2010
,
Count(distinct case when 2011 GE year(start) and 2011 LE year(end) then person else . end) as person_count_2011
from data;
quit;

This is example only for the years 2005 to 2011, but i want count person for more years.

Thank you for your effort!

Dynamike

---

[Dynamike]

Hi thank you for your answer and sorry for not showing input and output data:

Input data looks like this:

Person    start                  end

1             02/01/2006       30/07/2009

2             04/04/2009      30/11/2011

3             05/04/2007       03/02/2011

Output data should look like this:

year     Count

2005     0

2006     1

2007     2

2008     2

2009     3

2010     2

2011     2

OR if this is not possible:

Count_2005   Count_2006  Count_2007  Count_2008 ….

0                     1                    2                   2

[koyelghosh]

@Dynamike Below code has significant room for improvement but it should be able to get the job done. Assuming your dataset is called have ..else edit in the SQL statement.

DATA MasterDataSet;
	FORMAT Year 8. PersonCount 8.;
	STOP;
RUN;

%MACRO CalcTotalForYear(YearStart, NumYearsAhead);
	%DO YEAR_Num=0 %TO &NumYearsAhead;

		PROC SQL;
			CREATE TABLE TempDataSet AS SELECT &YearStart+&Year_Num AS Year, COUNT(*) AS 
				PersonCount FROM have WHERE YEAR(Start)<=&YEARStart+&Year_Num AND 
				YEAR(END)>=&YEARStart+&Year_Num;
		QUIT;
		DATA MasterDataSet;
			SET MasterDataSet TempDataSet;
		RUN;

	%END;
%MEND;

/* Change below for starting year and number of years ahead */
%CalcTotalForYear(2005, 7);

Ultimately it should give a MasterDataSet. Please let me know if it worked.

[koyelghosh]

With the code above, I got the following output.

Output

I used the macro as 

%CalcTotalForYear(2005, 6);

As said before, I have assumed that the dataset you have is conventionally named as "have"

[Reeza]

Does everyone have an end date? How many years are you expecting?

[Reeza]

You could execute the file if you also have Stata on the machine. It's the equivalent of calling the do file via command line or in batch, but not like running it in Stata.

Beyond that I haven't seen very much integration with Stata from SAS.

[Tom]

It might be easier to generate a dataset with all of the years (intervals) that you want to find counts for.

data years;
 do year=2005 to 2011 ;
   firstday=mdy(1,1,year);
   lastday=mdy(12,31,year);
   output;
 end;
 format firstday lastday yymmdd10.;
run;

Then join that with the data and generate the counts.

proc sql ;
  create table tall as
  select year,count(distinct person) as count
  from years a
  left join have b
  on a.firstday <= b.end and a.lastday >= b.start
  group by year
  order by year
  ;
quit;

[Tom]

That sees like a reasonable way to do it. 

You can easily generate that code with a %DO loop inside of a macro.

%macro year_count(in=,out=,start=,end=);
%local year sep;
proc sql noprint;
  create table &out as select
%do year=&start %to &end;
  &sep. count(distinct case when (&year between year(start) and year(end)) then person end)
    as person_count_&year
  %let sep=,;
%end;
  from &in
;
quit;
%mend year_count;

So to recreate your example use a call like this:

%year_count(in=data,out=test,start=2005,end=2011);

[Reeza]

Don't you usually need to account for how long a person is present within a year as well? Ie if you join in July 1, you're only 0.5 person year, not a full person year.

[Dynamike]

Thank you for your advice. Counting the person within a year is only the first step.
Do you know how to add the sum of days per year for all person (which i can then divide easily by 365 days)

Thank you for your effort.