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
@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;
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.
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
@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
Are every single one of the variables in table A the same type, numeric or character?
@ballardw Yes all character
@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;
Many thanks! Have a good evening 🙂
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.