topic Re: Merging two datasets with different number of rows per id in SAS Programming

Merging two datasets with different number of rows per id

SarahW13 — Tue, 05 Jun 2018 01:55:33 GMT

Hello all!

I have two datasets with a different number of rows per person_id, and I would like to merge the two datasets in a way that each row from dataset B gets added to all rows for that corresponding person in dataset A.

Below is an example of what the original datasets would look like:

DATASET A:

id value

1 A

1 B

1 C

2 A

2 C

DATASET B:

id med start_date end_date

1 1 1/5/13 2/10/16

1 2 3/10/16 4/10/16

2 1 1/8/13 1/10/13

2 2 3/5/18 5/3/18

2 3 5/5/18 6/1/18

This is what I would like the merged dataset to look like:

id value med start_date end_date

1 A 1 1/5/13 2/10/16

1 A 2 3/10/16 4/10/16

1 B 1 1/5/13 2/10/16

1 B 2 3/10/16 4/10/16

1 C 1 1/5/13 2/10/16

1 C 2 3/10/16 4/10/16

2 A 1 1/8/13 1/10/13

2 A 2 3/5/18 5/3/18

2 A 3 5/5/18 6/1/18

2 C 1 1/8/13 1/10/13

2 C 2 3/5/18 5/3/18

2 C 3 5/5/18 6/1/18

Can anyone advise me how I can merge the two datasets so that all rows for each person in dataset B gets added to each row for that same person in dataset A?

Thank you for your help!

Re: Merging two datasets with different number of rows per id

Astounding — Tue, 05 Jun 2018 02:01:56 GMT

The easiest way is PROC SQL:

proc sql;

create table want as

select a.value, b.* where a.id = b.id;

quit;

Re: Merging two datasets with different number of rows per id

SarahW13 — Tue, 05 Jun 2018 02:07:50 GMT

Astounding, thanks for the reply!

Is there any way to actually do this as a data step without creating a table in proc sql? My understanding about proc sql tables is that I can't actually use that table as a dataset. I want to do this merge in a way that creates an actual dataset that I can manipulate further.

Re: Merging two datasets with different number of rows per id

Reeza — Tue, 05 Jun 2018 03:38:41 GMT

Proc SQL creates datasets if you have the CREATE TABLE portion. The SELECT alone does not.

Re: Merging two datasets with different number of rows per id

SarahW13 — Tue, 05 Jun 2018 04:04:09 GMT

I didn't know that (about the create table option).

The code from Astounding doesn't seem to be working when I try it. I think I'm just not using it correctly. Astounding (or anyone else), could you clarify that code for me? For the code a.value, would "a" be referring to the dataset name and "value" referring to the variable name?

In the example datasets that I gave, I would be trying to copy all columns (value, med, start_date, end_date), with the four values for each id getting added to all rows for that corresponding id from the other dataset.

Re: Merging two datasets with different number of rows per id

Kurt_Bremser — Tue, 05 Jun 2018 06:25:34 GMT

@Astounding's code is missing the "from" part of the create table/select statement:

proc sql;
create table want as
select a.value, b.*
from a, b
where a.id = b.id
;
quit;

Re: Merging two datasets with different number of rows per id

ruchi11dec — Tue, 05 Jun 2018 11:12:43 GMT

I don't think merge will give you the desired result, the best way to do this is by proc sql.

Re: Merging two datasets with different number of rows per id

Astounding — Tue, 05 Jun 2018 11:19:47 GMT

Good catch, @Kurt_Bremser

Regarding the other concerns, such as creating a table, being able to use it, all of those should be fine. Just test it on your own test data to verify for yourself.

Re: Merging two datasets with different number of rows per id

Reeza — Tue, 05 Jun 2018 14:48:44 GMT

@SarahW13 wrote:

I didn't know that (about the create table option).

The code from Astounding doesn't seem to be working when I try it. I think I'm just not using it correctly. Astounding (or anyone else), could you clarify that code for me? For the code a.value, would "a" be referring to the dataset name and "value" referring to the variable name?

In the example datasets that I gave, I would be trying to copy all columns (value, med, start_date, end_date), with the four values for each id getting added to all rows for that corresponding id from the other dataset.

If something isn't working please post the non working code and the log at minimum.