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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: