I have two files: file1 has 300 companies, with id 1 to 300, file 2 has 600 products. How can I make a macro so that I have a file with each (300) firm with 600 products? Thanks so much!
I wrote following macro codes named time, but it does not work. (My logic is: pick out each firm first, merge it with file 2, so that I have 300 files each with 600 firms. Then I can just merge these 300 files together into what I need.)
data dd&i; set file1;
%do i=1 %to 300; if id_use=&i;
data final&i; merge file2 dd&i; by year quarter;
Not that I understand the business logic behind having all 600 hundred products joined with all 300 companies - but this would be called a cartesian product which is best produced using SQL syntax.
No need to use macro code.
Hope the following example will give you an idea how to achieve what you're after.
do company_id=1 to 300;
do product_id=1 to 600;
create table CompanyProduct as
P.S: In "normal" life one would have 3 tables: "company", "product" and a bridge table "CompanyProduct".
The bridge table would contain the keys for "company" and "product" for the cases where a company also offers this product. There might also be additional bridge informations in the table like the date when the company started to offer the product,... and so on.