topic Re: Merging yearly counts by identifier and summing the counts in SAS Programming

Merging yearly counts by identifier and summing the counts

Halp — Tue, 19 Jun 2018 13:02:27 GMT

Hello,

I need to merge 5 data sets that have the same variables but are just counts from different years. I need a single data set of all 5 years with their counts summed. All the individual years have this format for example:

2008:

tract	under 1	1-4	4-5	…
1	1	5	10	…
2	2	5	1	…
3	10	4	2	…
4	11	6	8	…
5	8	1	14	…
…	…	…	…	…

I need to put 5 the years together by the tract number and sum the variables so it come out like this for example:

2008-2012

tracts	under 1	1-4	4-5	…
1	151	418	410	…
2	541	115	231	…
3	122	354	354	…
4	78	456	564	…
5	6546	48	684	…
…	…	…	…	…

I've tried the simple

data Y_08_15;

merge D2008 D2009 D2010 D2011 D2012;

by Tracts;

run;

But the sums of the variables did not come out right. Like for age 5 the sum should've been 215 but came out as 18. I am not sure what I am missing or need to do differently, what is the code I should be using?

Thanks in advanced!

Re: Merging yearly counts by identifier and summing the counts

PaigeMiller — Tue, 19 Jun 2018 13:11:13 GMT

UNTESTED CODE

data want;
    set d2008 d2009 d2010 d2011 d2012;
run;

proc summary data=want nway;
    class tract;
    var under1 _1_to_4 _4_to_5 ... ;
    output out=want2 sum=;
run;

Re: Merging yearly counts by identifier and summing the counts

Amir — Tue, 19 Jun 2018 13:35:23 GMT

Hi,

How about something like the following untested code, which you should be able to use for any span of years:

%macro merge_years(from_year,to_year);
   data want;
      %do i = &from_year %to &to_year;
         set D&i;
         total_under_1 = sum(0,total_under_1,under_1);
         total_1_4     = sum(0,total_1_4,'1-4'n);
         total_4_5     = sum(0,total_4_5,'4-5'n);
      %end;
   run;
%mend merge_years;

%merge_years(2008,2012);

Regards,

Amir.

Re: Merging yearly counts by identifier and summing the counts

Halp — Tue, 19 Jun 2018 15:30:16 GMT

Thank you!