DATA Step, Macro, Functions and more

Merging data sets

Reply
Occasional Contributor
Posts: 11

Merging data sets

Hello All,

I have two datasets as follows:

dataset A

name

john

tom

Emily

dataset B

ID

34

54

67

I would like to merge these datasets to get the following dataset.

datasetC

name id

John 34

John 54

John 67

Tom 34

Tom 54

Tom 67

Emily 34

Emily 54

Emily 67

How can I achieve this? Thanks for any help.

Respected Advisor
Posts: 3,156

Merging data sets

just join without conditons, SQL will automatically do that.

data have1;

infile cards;

input name $;

cards;

john

tom

Emily

;

data have2;

infile cards;

input id;

cards;

34

54

67

;

proc sql;

create table want as

select * from have1

full join

have2 on 1

;

quit;

proc print;run;

Regards,

Haikuo

Regular Contributor
Posts: 184

Re: Merging data sets

A slightly more straightforward FROM Clause:

from have1

cross join

have2

Super Contributor
Posts: 1,636

Merging data sets

another way:

proc sql noprint;

  select id into :ids  separated by ',' from have2;

quit;

data want ;

set have1;

do _n_=&ids;

id=_n_;

output;

end;

run;

Linlin

Respected Advisor
Posts: 3,156

Re: Merging data sets

Well, if you are really keen on using datastep and don't mind flagging _error_ variable, here is another way :

datawant;

set have1;

n=0;

do while (1);

n+1;

set have2 point=n ;

if_error_ then leave;

output;

end;

run;

Kindly Regards,

Haikuo

Super Contributor
Posts: 1,636

Re: Merging data sets

Thank you Haikuo!

Another way:

data have1;

infile cards;

input name $;

cards;

john

tom

Emily

;

data have2;

infile cards;

input id;

cards;

34

54

67

;

data want;

  set have1;

    do i=1 to 3;

      set have2 point=i;

      _n_=i;

  output;

  end;

run;

proc print;run;

Super User
Super User
Posts: 7,039

Re: Merging data sets

You can eliminate the hardcoded 3 by using the NOBS option on the second SET statement.

data want;

  set have1;

  do i=1 by 1 until (i=nobs);

    set have2 point=i nobs=nobs;

    output;

  end;

run;

Super Contributor
Posts: 1,636

Re: Merging data sets

Nice. Thank you Tom.

Respected Advisor
Posts: 3,156

Re: Merging data sets

Thanks!  Tom's code is definitely better than flagging _error_. Tom's code can also be further simplified  by removing 'by 1 until':

data want;

  set have1;

  do i=1 to nobs;

    set have2 point=i nobs=nobs;

    output;

  end;

run;

Kindly Regards,

Haikuo

Frequent Contributor
Posts: 117

Merging data sets

/*Dataset A*/

data A;

input name $;

cards;

john

tom

emily

;

run;

/*Dataset B*/

data B;

input ID;

cards;

34

54

67

;

run;

/*using cross join to perform one-to-many match*/

proc sql;

create table C as select * from A,B;

quit;

/*print the dataset C*/

proc print data=C;

run;

Regards,

Vishnu

Ask a Question
Discussion stats
  • 9 replies
  • 285 views
  • 6 likes
  • 6 in conversation