BookmarkSubscribeRSS Feed
maggie00
Calcite | Level 5
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;
2 REPLIES 2
Patrick
Opal | Level 21
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.
maggie00
Calcite | Level 5
Thanks so much! That worked! I appreciate your help!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 726 views
  • 0 likes
  • 2 in conversation