DATA Step, Macro, Functions and more

Merging datasets without common variables

Reply
Occasional Contributor Rik
Occasional Contributor
Posts: 12

Merging datasets without common variables

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.
Respected Advisor
Posts: 4,173

Re: Merging datasets without common variables

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;
Occasional Contributor Rik
Occasional Contributor
Posts: 12

Re: Merging datasets without common variables

How simple can it be?

Thanks for your help!
SAS Employee
Posts: 73

Re: Merging datasets without common variables

proc sql;
select * from one,two,three;
quit;
Ask a Question
Discussion stats
  • 3 replies
  • 151 views
  • 0 likes
  • 3 in conversation