BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
avepo
Fluorite | Level 6

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

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

8 REPLIES 8
novinosrin
Tourmaline | Level 20

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

avepo
Fluorite | Level 6

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
Tourmaline | Level 20

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

Kurt_Bremser
Super User

@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
Fluorite | Level 6

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
Tourmaline | Level 20

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_
Rhodochrosite | Level 12

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
SAS Super FREQ

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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