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 🙂
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.