DATA Step, Macro, Functions and more

Merge datasets

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

Merge datasets

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

 

 


Accepted Solutions
Solution
‎05-19-2018 10:12 PM
SAS Super FREQ
Posts: 9,371

Re: Merge datasets

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):

how_set_statement_concatenates.png

 

  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:

how_merge_horizontal.png

 

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

how_merge_horizontal2.png

 

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

 

Cynthia

View solution in original post


All Replies
PROC Star
Posts: 1,836

Re: Merge datasets

[ Edited ]

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

Occasional Contributor
Posts: 10

Re: Merge datasets

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

PROC Star
Posts: 1,836

Re: Merge datasets

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

Super User
Posts: 10,280

Re: Merge datasets


@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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 10

Re: Merge datasets

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

PROC Star
Posts: 1,836

Re: Merge datasets

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

PROC Star
Posts: 514

Re: Merge datasets

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

 

Proc sql;

select id, date from dataset1

union 

select id, date from dataset2;

 

Solution
‎05-19-2018 10:12 PM
SAS Super FREQ
Posts: 9,371

Re: Merge datasets

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):

how_set_statement_concatenates.png

 

  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:

how_merge_horizontal.png

 

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

how_merge_horizontal2.png

 

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

 

Cynthia

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 253 views
  • 0 likes
  • 5 in conversation