SAS: replicate rows and iterate over columns

[ToRsy]

I'd like to replicate and modify specific rows in the table.

before:

xyz_id | letter | Col_1 | Col_2|
1 | Z | V1 | W1 |
2 | Z | V2 | W2 |
3 | Z | V3 | W3 |

after:

xyz_id | letter | Col_1 | Col_2|
1 | A | V1.1 | W1.1 |
1 | B | V1.1 | W1.1 |
1 | C | V1.1 | W1.1 |
2 | A | V2.1 | W2.1 |
2 | B | V2.1 | W2.1 |
2 | C | V2.1 | W2.1 |
3 | A | V3.1 | W3.1 |
3 | B | V3.1 | W3.1 |
3 | C | V3.1 | W3.1 |

I've prepared the following code:

data test;
input xyz_id$ letter$ Col_1 Col_2;
cards;
1 Z 0.1895 . 
2 Z 0.2208 0.2432
3 Z 0.3696 0.3739
;
run;

data test2;
        set test;
        
        array letters {8} $8 _temporary_ ('A', 'B', 'C');
        array weights {8}  _temporary_ (1,2,3);
        array nvars {2} Col_1 Col_2;

        do i = 1 to 8;
            letter = letters(i);

            do j=1 to 2;
                nvar{j} = nvar{j} * weights(i);
            end;

            output;
        end;
          drop i;
run;

but it doesn't work. Any suggestions?

[Kurt_Bremser]

"V1" or "W1" are not numeric values, so you cannot use them in calculations. Please post your example data in unambiguous manner, which means in a data step with datalines (do not skip this!) so we know exactly what we have to deal with in terms of values and variable attributes.

[ToRsy]

@Kurt_Bremser - I added the data step - sorry for the inconvenience

[Kurt_Bremser]

OK, so, based on this dataset:

data test;
input xyz_id$ letter$ Col_1 Col_2;
cards;
1 Z 0.1895 . 
2 Z 0.2208 0.2432
3 Z 0.3696 0.3739
;

what do you expect to get?

[yabwon]

Wouldn't an SQL product be what you need?

data test;
input xyz_id$ letter$ Col_1 Col_2;
cards;
1 Z 0.1895 . 
2 Z 0.2208 0.2432
3 Z 0.3696 0.3739
;
run;

data letters;
input letter $;
cards;
A
B
C
;
run;

proc sql;
create table test2 as
select 
 a.xyz_id
,b.letter
,a.Col_1
,a.Col_2
from 
test as a, letters as b
order by
 a.xyz_id
,b.letter
;
quit;
proc print data = test2;
run;

Bart

[ToRsy]

Hi Bart, not really. There might be several different Col_. I'm looking for a tool.

[yabwon]

What do you mean by " several different Col_" ?

Bart

[yabwon]

Does each letter (a b c) can have a weight assigned to it? Like here:

data test;
input xyz_id$ letter$ Col_1 Col_2;
cards;
1 Z 0.1895 . 
2 Z 0.2208 0.2432
3 Z 0.3696 0.3739
;
run;

data letters;
input letter $ weight;
cards;
A 10
B 20
C 30
;
run;

proc sql;
create table test2 as
select 
 a.xyz_id
,b.letter
,a.Col_1 * b.weight as Col_1
,a.Col_2 * b.weight as Col_2
from 
test as a, letters as b
order by
 a.xyz_id
,b.letter
;
quit;
proc print data = test2;
run;

Bart