topic Merging datasets and summing one column in New SAS User

Merging datasets and summing one column

mitrakos — Thu, 22 Oct 2020 19:58:01 GMT

Hello!

I am trying to merge two datasets together that have the same variable names, but different observations. I would like to summate one of the columns. Example is given below.

One dataset:

date1	COUNT	PERCENT
19W42	4	4
19W43	2	2
19W44	6	6
19W45	3	3

Second dataset:

date1	COUNT	PERCENT
19W43	1	1.041
19W44	5	5.20
19W45	6	6.25
19W46	0	0

I would like to combine them so that the resulting dataset adds the COUNT column as so:

date1	COUNT
19W42	4
19W43	3
19W44	11
19W45	9
19W46	0

The code I'm currently using merges the two datasets but doesn't add the COUNT column. I suspect my count=count+count portion does not do anything and I'm missing something. Is anyone able to lend a hand?

data merging;
	merge alldates dash.onezerotwo;
	by date1;
	output;
	count=count+count;
run;

Thank you!

Re: Merging datasets and summing one column

Kurt_Bremser — Thu, 22 Oct 2020 20:34:09 GMT

First, combine the datasets, then run proc summary:

data all;
set
  one
  two
;
by date1;
run;

proc summary data=all;
by date1;
var count;
output out=want sum()=;
run;

Re: Merging datasets and summing one column

ballardw — Thu, 22 Oct 2020 23:08:02 GMT

@mitrakos wrote:

Hello!

I am trying to merge two datasets together that have the same variable names, but different observations. I would like to summate one of the columns. Example is given below.

One dataset:

date1 COUNT PERCENT

19W42 4 4

19W43 2 2

19W44 6 6

19W45 3
3

Second dataset:

date1 COUNT PERCENT

19W43 1 1.041

19W44 5 5.20

19W45 6 6.25

19W46 0 0

I would like to combine them so that the resulting dataset adds the COUNT column as so:

date1 COUNT

19W42 4

19W43 3

19W44 11

19W45 9

19W46 0

The code I'm currently using merges the two datasets but doesn't add the COUNT column. I suspect my count=count+count portion does not do anything and I'm missing something. Is anyone able to lend a hand?
data merging;
	merge alldates dash.onezerotwo;
	by date1;
	output;
	count=count+count;
run;
Thank you!

It can be done but you have three strikes to over come. First is merge yields only get one resulting variable when both sets contribute like named variables other than the BY variable. The second is that some would have missing count and when you use + with missing the result is missing. Third is that with an OUTPUT statement before the sum step the summed result is not sent to the output data set.

data merging;
	merge alldates dash.onezerotwo (rename=(count=count102));
	by date1;

	newcount=sum(count,count102);
run;

I created an entirely new variable so you could check the results. After verifying this works then change newcount to count and drop cout102.

Possible but if you have 20 of these sets to combine you'll find that @Kurt_Bremser's will be much simpler to code.