DATA Step, Macro, Functions and more

Merging yearly counts by identifier and summing the counts

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

Merging yearly counts by identifier and summing the counts

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:

tractunder 11-44-5
11510
2251
31042
41168
58114

 

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

tractsunder 11-44-5
1151418410
2541115231
3122354354
478456564
5654648684

 

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!


Accepted Solutions
Solution
‎06-19-2018 11:29 AM
Respected Advisor
Posts: 3,055

Re: Merging yearly counts by identifier and summing the counts

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;
--
Paige Miller

View solution in original post


All Replies
Solution
‎06-19-2018 11:29 AM
Respected Advisor
Posts: 3,055

Re: Merging yearly counts by identifier and summing the counts

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;
--
Paige Miller
New Contributor
Posts: 2

Re: Merging yearly counts by identifier and summing the counts

Posted in reply to PaigeMiller
Thank you!
Super Contributor
Posts: 340

Re: Merging yearly counts by identifier and summing the counts

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 88 views
  • 4 likes
  • 3 in conversation