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!
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;
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;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.