topic Re: Merging datasets without common variables in SAS Programming

Merging datasets without common variables

Rik — Mon, 06 Oct 2008 06:56:41 GMT

Hi,

I want to create a dataset with information coming out of multiple datasets Let me try to explain with an example;

Data ONE;
infile cards;
attrib COUNTRY length=$20 label='Country';
input country;
cards;
Belgium
France
Germany
;
run;
Data TWO;
infile cards;
attrib STOCK length=$20 label='Stock';
input country;
cards;
Stock1
Stock2
;
run;
Data THREE;
infile cards;
attrib PRODUCT length=$20 label='Product';
input country;
cards;
Product1
Product2
;
Run;

Now I want to create a dataset with all possible combinations of the above data set. This should look like this.
COUNTRY STOCK PRODUCT
--------------- ------------- ----------------
Belgium Stock1 Product1
Belgium Stock1 Product2
Belgium Stock2 Product1
Belgium Stock2 Product2
France Stock1 Product1
France Stock1 Product2
France Stock2 Product1
France Stock2 Product2
Germany Stock1 Product1
Germany Stock1 Product2
Germany Stock2 Product1
Germany Stock2 Product2

Not sure how to handle this.
Any suggestions?

Thanks.

Re: Merging datasets without common variables

Patrick — Mon, 06 Oct 2008 07:10:52 GMT

What you want is a cartesian product. That's easiest done with SQL:

Data ONE;
infile cards;
attrib COUNTRY length=$20 label='Country';
input country;
cards;
Belgium
France
Germany
;
run;
Data TWO;
infile cards;
attrib STOCK length=$20 label='Stock';
input STOCK;
cards;
Stock1
Stock2
;
run;
Data THREE;
infile cards;
attrib PRODUCT length=$20 label='Product';
input PRODUCT;
cards;
Product1
Product2
;
Run;

proc sql;
select *
from THREE,(select * from one ,two)
;
quit;

Re: Merging datasets without common variables

Rik — Tue, 07 Oct 2008 05:38:50 GMT

How simple can it be?

Thanks for your help!

Re: Merging datasets without common variables

Pavan_SAS — Tue, 07 Oct 2008 09:04:56 GMT

proc sql;
select * from one,two,three;
quit;