DATA Step, Macro, Functions and more

Setting a dataset of each row of another dataset

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

Setting a dataset of each row of another dataset

Hi Everyone,

   I have one dataset which has one variable, containing a person's name and another dataset with a list of training courses and I was wondering if there is a quick way to create a third dataset which would contain a record for each possible person and training combination.  The two datasets do not have a common variable.  Keep in mind that we may not know how many of each we have at any time, and the data can change constantly.  For example:

 

Dataset A:

Name

 Abby

Bobby

Chris

 

Dataset B:

Training

Training #1

Training #2

 

Desired Dataset:

Name        Training

Abby          Training #1

Abby          Training #2

Bobby        Training #1

Bobby        Training #2

Chris          Training #1

Chris          Training #2

 

  Any thoughts?

 

Thanks!

 

-Phil


Accepted Solutions
Solution
‎03-03-2017 05:36 PM
Super User
Posts: 19,870

Re: Setting a dataset of each row of another dataset

It's called a cartesian join or cross join.

 

 

proc sql;

create table want as

select *

from a,b;

quit;

View solution in original post


All Replies
Solution
‎03-03-2017 05:36 PM
Super User
Posts: 19,870

Re: Setting a dataset of each row of another dataset

It's called a cartesian join or cross join.

 

 

proc sql;

create table want as

select *

from a,b;

quit;

Occasional Contributor
Posts: 15

Re: Setting a dataset of each row of another dataset

Thanks!  That worked perfectly!  It did give me a "NOTE: The execution of this query involves performing one or
more Cartesian product joins that can not be optimized." message, but it did seem to create all of the desired records.

Trusted Advisor
Posts: 1,301

Re: Setting a dataset of each row of another dataset

 

data a;
input name $;
cards;
abby
bobby
chris
;
run;

data b;
length training $ 11;
input training &;
cards;
training #1
training #2
;
run;

data c;
set a;
do _n_=1 to nobs;
  set b nobs=nobs point=_n_;
  output;
end;
run;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 151 views
  • 2 likes
  • 3 in conversation