I have the table "Medium_table" which contains information about the "type" "medium" in the second table "Product_table". So I want to add the rows in the "Medium_table" to each row where type=medium in the "Product_table" table. There is a very specific example below where the table I want is called "Resulting_table"
Medium_table
Size Code1 Code2 Code3 Code4
41 0983 9903 3339 3490
42 1048 2093 3988 3100
43 2311 1110 1101 4444
44 6780 1033 4789 0002
Product_table
Id Type Location
1 Medium SE
2 Large FI
3 Medium NO
4 Medium US
5 Small ES
6 Medium FR
7 Small DE
8 Small DK
Resulting_Table
Id Type Location Size Code1 Code2 Code3 Code4
1 Medium SE 41 0983 9903 3339 3490
1 Medium SE 42 1048 2093 3988 3100
1 Medium SE 43 2311 1110 1101 4444
1 Medium SE 44 6780 1033 4789 0002
3 Medium NO 41 0983 9903 3339 3490
3 Medium NO 42 1048 2093 3988 3100
3 Medium NO 43 2311 1110 1101 4444
3 Medium NO 44 6780 1033 4789 0002
4 Medium US 41 0983 9903 3339 3490
4 Medium US 42 1048 2093 3988 3100
4 Medium US 43 2311 1110 1101 4444
4 Medium US 44 6780 1033 4789 0002
6 Medium FR 41 0983 9903 3339 3490
6 Medium FR 42 1048 2093 3988 3100
6 Medium FR 43 2311 1110 1101 4444
6 Medium FR 44 6780 1033 4789 0002
Use set with the point=option:
data result;
set product_table;
where type = 'Medium';
do i = 1 to nobs;
set medium_table point=i nobs=nobs;
output;
end;
drop i;
run;
Post test data in the form of a datastep, hence this is untested:
data medium_table; set medium_table; type="Medium"; run; data want; merge product_table (where=(type="Medium")) medium_table; by type; run;
A very simple method would be to build a cartesian join in SQL:
proc sql;
create table result2 as
select *
from product_table a, medium_table b
where a.type = "Medium";
quit;
Or you could use a hash object for the medium table:
data result3;
set product_table;
where type = 'Medium';
attrib
size length=8
code1-code4 length=$8
;
if _n_ = 1
then do;
declare hash h(dataset:'work.medium_table',ordered:'yes');
h.definedata('size','code1','code2','code3','code4');
h.definekey('size');
h.definedone();
declare hiter hi('h');
call missing(size,code1,code2,code3,code4);
end;
rc = hi.first();
do until (rc ne 0);
output;
rc = hi.next();
end;
drop rc;
run;
data medium;
input Size (Code1 Code2 Code3 Code4 ) ($);
cards;
41 0983 9903 3339 3490
42 1048 2093 3988 3100
43 2311 1110 1101 4444
44 6780 1033 4789 0002
;
data product;
input Id Type $ Location $;
cards;
1 Medium SE
2 Large FI
3 Medium NO
4 Medium US
5 Small ES
6 Medium FR
7 Small DE
8 Small DK
;
data want;
set product(where=(type='Medium'));
if _n_=1 then do;
if 0 then set medium;
dcl hash H (dataset:'medium',ordered:'a') ;
h.definekey ('size') ;
h.definedata(all:'y');
h.definedone () ;
declare hiter hi('h');
end;
do while(hi.next()=0);
output;
end;
run;
proc sql;
create table want as
select a.*,size,Code1,Code2,Code3, Code4
from product(where=(type='Medium')) a inner join (select *,"Medium" as Type from medium)b
on a.type=b.type
order by a.id,b.size;
quit;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.