topic Re: Insert rows according to another table rows values in SAS Programming

Insert rows according to another table rows values

fabdu92 — Fri, 10 Feb 2017 15:29:36 GMT

Hi,

I have two tables A and B

In table A I have only one row with an undetermined number of column.

In my table B I have two columns ID, VALUE with 0 rows.

I would like to insert three rows for each of the A column

The ID would be 1, 2, and 3. The VALUE would be equal to each value of the first (and only row) of table A.

Exemple My table A is like this:

Col1 Col2 Col3 Col4 ...

ZZS KJP UOI RJS ...

My table B should be something like

ID VALUE

1 ZZS

2 ZZS

3 ZZS

1 KJP

2 KJP

3 KJP

1 UOI

2 UOI

3 UOI

1 RJS

2 RJS

3 RJS

...

As I am a crap in SAS macro I have truly no idea of how to do it...Can you help me?

Thanks

Re: Insert rows according to another table rows values

LinusH — Fri, 10 Feb 2017 15:36:28 GMT

Create a table C, and manually/programmatically enter three rows for the column ID (1, 2, & 3).

The create B as a join A with C without joining criteria, hence creating a Cartesian product.

Re: Insert rows according to another table rows values

Kurt_Bremser — Fri, 10 Feb 2017 15:37:44 GMT

Use array processing:

data table_a;
input col1 $ col2 $ col3 $ col4 $;
cards;
ZZS KJP UOI RJS
;
run;

data table_b (keep=id value);
set table_a;
array cols {*} col1-col4;
do i1 = 1 to dim(cols);
  do id = 1 to 3;
    value = cols{i1};
    output;
  end;
end;
run;

proc print data=table_b noobs;
run;

Result:

id    value

 1     ZZS 
 2     ZZS 
 3     ZZS 
 1     KJP 
 2     KJP 
 3     KJP 
 1     UOI 
 2     UOI 
 3     UOI 
 1     RJS 
 2     RJS 
 3     RJS

Re: Insert rows according to another table rows values

ballardw — Fri, 10 Feb 2017 15:37:45 GMT

Are every single one of the variables in table A the same type, numeric or character?

Re: Insert rows according to another table rows values

fabdu92 — Fri, 10 Feb 2017 15:46:22 GMT

@Kurt_BremserThanks for the reply. The problem, in this case is I don't know how many column I wil have. In the example it was 4 but it can be 2, 3, 4, 5, 6, 7...depending on what data I receive

Re: Insert rows according to another table rows values

fabdu92 — Fri, 10 Feb 2017 15:48:23 GMT

@ballardw Yes all character

Re: Insert rows according to another table rows values

ballardw — Fri, 10 Feb 2017 16:10:37 GMT

@fabdu92 wrote:

@ballardw Yes all character

Then @Kurt_Bremser's solution with one very minor change should work. The special list variable _character_ references all character variables in the data set.

data table_b (keep=id value);
set table_a;
array cols {*} _character_;
do i1 = 1 to dim(cols);
  do id = 1 to 3;
    value = cols{i1};
    output;
  end;
end;
run;

Re: Insert rows according to another table rows values

fabdu92 — Fri, 10 Feb 2017 16:20:50 GMT

Many thanks! Have a good evening 🙂