BookmarkSubscribeRSS Feed
bkoksal
Calcite | Level 5

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.

9 REPLIES 9
Haikuo
Onyx | Level 15

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
Quartz | Level 8

A slightly more straightforward FROM Clause:

from have1

cross join

have2

Linlin
Lapis Lazuli | Level 10

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
Onyx | Level 15

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
Lapis Lazuli | Level 10

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
Super User Tom
Super User

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
Lapis Lazuli | Level 10

Nice. Thank you Tom.

Haikuo
Onyx | Level 15

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
Lapis Lazuli | Level 10

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

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
  • 9 replies
  • 1326 views
  • 6 likes
  • 6 in conversation