add 4 rows to 1 row in SAS

[rhapsody]

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

[Kurt_Bremser]

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;

[RW9]

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;

[Kurt_Bremser]

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;

[novinosrin]

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;

[novinosrin]

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;