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.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.