topic Re: add 4 rows to 1 row in SAS in SAS Programming

add 4 rows to 1 row in SAS

rhapsody — Thu, 29 Nov 2018 13:23:00 GMT

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

Re: add 4 rows to 1 row in SAS

Kurt_Bremser — Thu, 29 Nov 2018 13:45:46 GMT

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;

Re: add 4 rows to 1 row in SAS

RW9 — Thu, 29 Nov 2018 13:46:10 GMT

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;

Re: add 4 rows to 1 row in SAS

Kurt_Bremser — Thu, 29 Nov 2018 14:05:44 GMT

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;

Re: add 4 rows to 1 row in SAS

novinosrin — Thu, 29 Nov 2018 15:15:09 GMT

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;

Re: add 4 rows to 1 row in SAS

novinosrin — Thu, 29 Nov 2018 21:12:06 GMT


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;