topic Re: Setting a dataset of each row of another dataset in SAS Programming

Setting a dataset of each row of another dataset

Phil0917 — Fri, 03 Mar 2017 22:21:58 GMT

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

Re: Setting a dataset of each row of another dataset

Reeza — Fri, 03 Mar 2017 22:23:55 GMT

It's called a cartesian join or cross join.

proc sql;

create table want as

select *

from a,b;

quit;

Re: Setting a dataset of each row of another dataset

Phil0917 — Fri, 03 Mar 2017 22:37:35 GMT

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.

Re: Setting a dataset of each row of another dataset

FriedEgg — Fri, 03 Mar 2017 23:02:29 GMT

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;