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 🙂
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.