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?
"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.
@Kurt_Bremser - I added the data step - sorry for the inconvenience
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?
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
Hi Bart, not really. There might be several different Col_. I'm looking for a tool.
What do you mean by " several different Col_" ?
Bart
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
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.