topic Re: Merging data by ID and categorical variable in New SAS User

Merging data by ID and categorical variable

marin1hk — Wed, 13 Nov 2019 15:55:21 GMT

Is it possible to merge multiple observations into one observation based on ID and a categorical variable? Right now I have multiple observations for each record when I only need one observation per record ID for each level of a categorical variable, this is what I need to do:

Record ID: Category: Record ID (grouped): Category (grouped):

1 2 1 2

1 2

1 3 1 3

1 3

2 4 2 4

2 4

How do I take the first two columns and group them to create the second two columns?

Re: Merging data by ID and categorical variable

VDD — Wed, 13 Nov 2019 16:09:46 GMT

select distinct record_id, Category;

you could also add a counter to tell you how many record for each group.

Re: Merging data by ID and categorical variable

ballardw — Wed, 13 Nov 2019 16:12:32 GMT

It really is not clear what your output should be. Is it a report that people read or a data set? If a data set would it have 7 records with missing values for several or 3?

Also, are there other variables involved?

This may be one way:

proc sql;
   create table want as
   select distinct recordid, category
   from have;
quit;

Since you don't actually show normally acceptable SAS variable names I'm guessing on those.

Re: Merging data by ID and categorical variable

Bennyboy — Wed, 13 Nov 2019 16:23:02 GMT

Proc sort using nodukey option might also do what you want.

data have;
input recordID Category;
datalines;
1 2
1 2
1 3
1 3
1 3
2 4
2 4
;
run;

proc sort data = have nodupkey; by recordID category; run;

Re: Merging data by ID and categorical variable

Astounding — Wed, 13 Nov 2019 18:37:23 GMT

You have plenty of good suggestions that work for what you have presented.

However, suppose your data set contains additional variables and you would like to preserve some values of those other variables?

Here's a solution that best addresses the issue of "additional variables":

data want;
update have (obs=0) have;
by id category;
run;