topic Re: Please help me with the macro code with do loop in SAS Programming

Please help me with the macro code with do loop

maggie00 — Tue, 29 Dec 2009 23:18:17 GMT

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.)

%macro time;
data dd&i; set file1;
%do i=1 %to 300; if id_use=&i;
data final&i; merge file2 dd&i; by year quarter;
%end;
%mend time;
run;

Re: Please help me with the macro code with do loop

Patrick — Wed, 30 Dec 2009 00:12:13 GMT

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.

data company;
do company_id=1 to 300;
output;
end;
run;

data product;
do product_id=1 to 600;
output;
end;
run;

proc sql;
create table CompanyProduct as
select *
from company,product
;
quit;

HTH
Patrick

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.

Re: Please help me with the macro code with do loop

maggie00 — Wed, 30 Dec 2009 00:17:08 GMT

Thanks so much! That worked! I appreciate your help!