topic Merging two data sets in SAS Programming

Merging two data sets

mroy01 — Sat, 04 Aug 2018 01:26:32 GMT

Hi there,

I have a dataset with a number of same id and different observations for another dummy variable. I hope to make it so that there's one row for each id and the count becomes a sum of all of the observations. Such as

1 1

2 1

3 0

1 1

4 1

2 1

3 1

4 0

5 0

1 1

Becoming

1 3

2 2

3 1

4 1

5 0

Gratefully,

SAS 9.3

Re: Merging two data sets

Astounding — Sat, 04 Aug 2018 01:35:58 GMT

SAS knows how to do that:

proc summary data=have nway;

class id;

var dummy;

output out=want (keep=id total) sum=total;

run;

Re: Merging two data sets

kiranv_ — Sat, 04 Aug 2018 02:02:24 GMT

something like below

proc sql;

select id, sum(secondcolumn) as secondcolumn

from yourtable

group by id;

Re: Merging two data sets

mroy01 — Mon, 06 Aug 2018 16:28:33 GMT

Thank you for this tip! I forgot to mention that I'm hoping to keep other variables in the summary as well. Any ideas to achieve that?

Kindly,

Matt

Re: Merging two data sets

Astounding — Mon, 06 Aug 2018 19:50:45 GMT

You can keep other variables. But you have to choose from among more than one value. When you get a sum, that comes from many observations, but only one of the values of those other variables can be saved.

Take a look at MINID and MAXID. You can select whether you want the minimum or the maximum value of the "other" variables kept in the summary data set.