Hello, I am using SAS EG 7.1. I have two tables called customer and product. I want to cross join these tables and filtering in same step. View of input tables and table that i want to create is as below: In real; customer table have approx 10 millions rows, product table have approx 300 rows and i prefer to use datastep language because of it faster than proc sql language and i want to filter in same step for more efficiency. I tried below code but it doesn't work as i want. data want;
set customer;
do i = 1 to n;
set work.product point = i nobs = n;
if cust_var2 ne prod_id and cust_var1 le prod_var1;
output;
end;
run; Actually proc sql language that below works but it is very slow because of table size. proc sql;
create table want3 as select
*
from customer t1, product t2
where
t1.cust_var2 ne t2.prod_id and t1.cust_var1 le t2.prod_var1;
quit; Thank you data product;
infile datalines delimiter=',';
input prod_id product_name $ prod_var1;
datalines;
1, aaa, 100
2, bbb, 250
3, ccc, 200
4, ddd, 100
5, ddd, 250
;
run;
data customer;
infile datalines delimiter=',';
input cust_id cust_var1 cust_var2;
datalines;
1, 100, 2
2, 200, 5
3, 300, 5
;
run;
... View more