Merge datasets

[avepo]

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

[novinosrin]

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

[avepo]

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

[novinosrin]

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

[Kurt_Bremser]

---

@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;

[avepo]

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

[novinosrin]

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

[kiranv_]

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;

[Cynthia_sas · Accepted solution]

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