topic Re: Person Count for several years in New SAS User

Person Count for several years

Dynamike — Wed, 03 Jul 2019 13:16:28 GMT

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

Re: Person Count for several years

VDD — Wed, 03 Jul 2019 13:57:50 GMT

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

Re: Person Count for several years

Reeza — Wed, 03 Jul 2019 15:04:39 GMT

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

Re: Person Count for several years

Dynamike — Wed, 03 Jul 2019 15:42:08 GMT

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

Re: Person Count for several years

Dynamike — Wed, 03 Jul 2019 15:44:15 GMT

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.

Re: Person Count for several years

koyelghosh — Wed, 03 Jul 2019 15:48:17 GMT

@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.

Re: Person Count for several years

koyelghosh — Wed, 03 Jul 2019 15:56:39 GMT

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"

Re: Person Count for several years

Reeza — Wed, 03 Jul 2019 16:00:04 GMT

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

Re: Person Count for several years

Reeza — Wed, 03 Jul 2019 16:01:55 GMT

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.

Re: Person Count for several years

Tom — Wed, 03 Jul 2019 16:09:22 GMT

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);

Re: Person Count for several years

Tom — Wed, 03 Jul 2019 16:23:47 GMT

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;

Re: Person Count for several years

Reeza — Wed, 03 Jul 2019 18:54:40 GMT

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.

Re: Person Count for several years

Dynamike — Thu, 04 Jul 2019 04:38:19 GMT

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.