topic Re: Merge datasets in SAS Programming

Merge datasets

avepo — Sat, 19 May 2018 19:42:28 GMT

Hi,

I am trying to merge two datasets, But not getting the result I need.

Dataset1 Dataset2

ID Date ID Date

1 1/2/15 1 2/9/16

1 2/3/16 1 2/20/16

1 3/3/16 1 4/4/17

1 4/5/17

1 5/5/16

My code was (after sorting both datasets by ID)

data want;

merge dataset2 dataset1;

by id;

run;

My result contains only dataset2 with none of the dataset1.

I don't see anything wrong with this code.

I also tried to switch the order of the dataset such that my code was

data want;

merge dataset1 dataset2;

by id;

run;

Then I get 3 lines from dataset2 and last 2 lines from dataset 1 as if I can only have maximum of 5 lines when I merge the dataset.

Any thoughts on this problem?

Thanks

Re: Merge datasets

novinosrin — Sat, 19 May 2018 19:48:03 GMT

what results do you expect or want and what are you getting please?

Re: Merge datasets

avepo — Sat, 19 May 2018 19:51:54 GMT

I want all dates to be listed under ID 1 such that the merged dataset has

ID Date

1 1/2/15

1 2/3/16

1 3/3/16

1 4/5/17

1 5/5/16

1 2/9/16

1 2/20/16

1 4/4/17

But my output only contains dataset 1 and I did not use (in=) option

ID Date

1 1/2/15

1 2/3/16

1 3/3/16

1 4/5/17

1 5/5/16

Thanks

Re: Merge datasets

novinosrin — Sat, 19 May 2018 19:56:35 GMT

classic difference between proc sql join and datastep merge. Try sql join you should get what you want.

Re: Merge datasets

avepo — Sat, 19 May 2018 19:59:32 GMT

Thanks

I am not familiar with proc sql syntax, left join, right join , etc

Could you please write a simple sample sql syntax for me to use?

Thanks

Re: Merge datasets

Kurt_Bremser — Sat, 19 May 2018 20:00:12 GMT

@avepo wrote:

I want all dates to be listed under ID 1 such that the merged dataset has

ID    Date

1    1/2/15

1    2/3/16

1 3/3/16

1 4/5/17

1 5/5/16

1    2/9/16

1    2/20/16

1 4/4/17

But my output only contains dataset 1 and I did not use (in=) option

ID    Date

1    1/2/15

1    2/3/16

1 3/3/16

1 4/5/17

1 5/5/16

Thanks

That is not a merge at all, it's a concatenation.

data want;
set
  dataset1
  dataset2
;
run;

Re: Merge datasets

novinosrin — Sat, 19 May 2018 20:05:11 GMT

Yep my mistake as I misread your data. It's a simple append(concatenation) as @Kurt_Bremser corrected us. Go with his datastep solution. My apologies in any case for the wrong assumption

Re: Merge datasets

kiranv_ — Sat, 19 May 2018 21:03:05 GMT

you can use @Kurt_Bremser code. in proc sql you need use union

Proc sql;

select id, date from dataset1

union

select id, date from dataset2;

Re: Merge datasets

Cynthia_sas — Sun, 20 May 2018 00:20:19 GMT

Hi:

In Programming 1 (the free e-learning class) and Programming 2, we cover the concepts of "stacking" or "concatenating" datasets compared to merging (a join in SQL).

Here's an example of SET to create one dataset (at the top of the picture):

And at the bottom of the picture is an example of SET to create two datasets based on the value of a variable. Notice how the datasets are "stacked" vertically. This is called concatenating datasets in SAS. It is useful for bringing together multiple datasets that have the same structure. Another good example of this would be monthly sales datasets for JAN, FEB and MAR used in 1 SET statement to create a dataset called QTR1 or QTR2:

data qtr1;
   set JAN FEB MAR;
run;
  
data qtr2;
  set APR MAY JUN;
run;

And here is a conceptual example of a merge:

Here's a merge with some REAL data, producing 5 output tables:

Hope this helps clarify the difference between a SET and a MERGE with SAS.

Cynthia