How to combine datasets that have different # of observation within each ID

CynthiaWei — Thu, 22 May 2025 03:57:01 GMT

Hi Dear SAS Pros,

I have 2 or more datasets as follows:

data db1;

input id V1 ;

datalines;

1 0.5
1 0.6
1 0.7
1 0.8

2 0.7
2 0.5

3 0.4
3 0.8

4 0.6

;

data db2;

input id V2 ;

datalines;

1 2.5
1 2.6

2 2.7
2 2.5
2 2.6

4 2.6
4 2.1

;

And what I want for output is as follows:

id V1 V2
1 0.5 2.5
1 0.6 2.6
1 0.7 .
1 0.8 .

2 0.7 2.7
2 0.5 2.5
2 . 2.6

3 0.4 .
3 0.8 .

4 0.6 2.6
4 . 2.1

Thank you so much for any advice or help.

Best,

Re: How to combine datasets that have different # of observation within each ID

Tom — Thu, 22 May 2025 04:22:17 GMT

A normal MERGE will leave the last value read in from a "short" dataset unchanged in a BY group. That is the reason that it works properly to do a 1 to MANY merge.

For your input you would get.

Obs    id     V1     V2

  1     1    0.5    2.5
  2     1    0.6    2.6
  3     1    0.7    2.6
  4     1    0.8    2.6
  5     2    0.7    2.7
  6     2    0.5    2.5
  7     2    0.5    2.6
  8     3    0.4     .
  9     3    0.8     .
 10     4    0.6    2.6
 11     4    0.6    2.1

If you don't want the short datasets' values retained then add an explicit OUTPUT statement and a CALL MISSING() statement.

data want;
  merge db1 db2;
  by id;
  output;
  call missing(of _all_);
run;

Result

Obs    id     V1     V2

  1     1    0.5    2.5
  2     1    0.6    2.6
  3     1    0.7     .
  4     1    0.8     .
  5     2    0.7    2.7
  6     2    0.5    2.5
  7     2     .     2.6
  8     3    0.4     .
  9     3    0.8     .
 10     4    0.6    2.6
 11     4     .     2.1

NOTE: I did not attempt to download your attachment. If its contents are important then include it in the body of your message.

NOTE: Use the Insert SAS Code and/or the Insert Code icons to get pop-up windows where you can paste in (or type or edit) lines of code or data.

topic How to combine datasets that have different # of observation within each ID in SAS Programming

How to combine datasets that have different # of observation within each ID

Re: How to combine datasets that have different # of observation within each ID