DATA Step, Macro, Functions and more

Insert rows according to another table rows values

Accepted Solution Solved
Reply
Contributor
Posts: 41
Accepted Solution

Insert rows according to another table rows values

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


Accepted Solutions
Solution
‎02-10-2017 11:19 AM
Super User
Posts: 10,507

Re: Insert rows according to another table rows values


fabdu92 wrote:

@ballardw Yes all character


Then @KurtBremser'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;

View solution in original post


All Replies
Super User
Posts: 5,257

Re: Insert rows according to another table rows values

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.

Data never sleeps
Super User
Posts: 6,946

Re: Insert rows according to another table rows values

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 
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 41

Re: Insert rows according to another table rows values

@KurtBremserThanks 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

Super User
Posts: 10,507

Re: Insert rows according to another table rows values

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

 

 

 

 

 

 

Contributor
Posts: 41

Re: Insert rows according to another table rows values

@ballardw Yes all character

Solution
‎02-10-2017 11:19 AM
Super User
Posts: 10,507

Re: Insert rows according to another table rows values


fabdu92 wrote:

@ballardw Yes all character


Then @KurtBremser'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;
Contributor
Posts: 41

Re: Insert rows according to another table rows values

Many thanks! Have a good evening Smiley Happy

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 161 views
  • 3 likes
  • 4 in conversation