topic Merging data sets in SAS Programming

Merging data sets

bkoksal — Thu, 26 Jan 2012 14:59:16 GMT

Hello All,

I have two datasets as follows:

dataset A

name

john

tom

Emily

dataset B

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.

Merging data sets

Haikuo — Thu, 26 Jan 2012 15:08:29 GMT

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;

;

proc sql;

create table want as

select * from have1

full join

have2 on 1

;

quit;

proc print;run;

Regards,

Haikuo

Merging data sets

Linlin — Thu, 26 Jan 2012 15:35:22 GMT

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

Re: Merging data sets

Howles — Thu, 26 Jan 2012 15:56:33 GMT

A slightly more straightforward FROM Clause:

from have1

cross join

have2

Re: Merging data sets

Haikuo — Thu, 26 Jan 2012 17:03:37 GMT

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

Re: Merging data sets

Linlin — Fri, 27 Jan 2012 13:34:55 GMT

Thank you Haikuo!

Another way:

data have1;

infile cards;

input name $;

cards;

john

tom

Emily

;

data have2;

infile cards;

input id;

cards;

;

data want;

set have1;

do i=1 to 3;

set have2 point=i;

_n_=i;

output;

end;

run;

proc print;run;

Re: Merging data sets

Tom — Fri, 27 Jan 2012 14:00:47 GMT

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;

Re: Merging data sets

Linlin — Fri, 27 Jan 2012 14:18:26 GMT

Nice. Thank you Tom.

Re: Merging data sets

Haikuo — Fri, 27 Jan 2012 19:00:43 GMT

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

Merging data sets

Vish33 — Sat, 28 Jan 2012 14:09:32 GMT

/*Dataset A*/

data A;

input name $;

cards;

john

tom

emily

;

run;

/*Dataset B*/

data B;

input ID;

cards;

;

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