Merging data sets

[bkoksal]

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.

[Haikuo]

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

[Howles]

A slightly more straightforward FROM Clause:

from have1

cross join

have2

[Linlin]

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

[Haikuo]

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

[Linlin]

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;

[Tom]

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;

[Linlin]

Nice. Thank you Tom.

[Haikuo]

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

[Vish33]

/*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