Hi,
I have a table(import from excel- - 800 row) like this:
data t1;
input index $ num $ num2 $;
datalines;
var1 a+b b
var2 b b+c
run;
and the table tab1 is this:
Now I have another table:
data t2; input a b c; datalines; 1 2 3 4 5 6 run;
like this:
- In table t2, I would like calculate the sum of the variable that are described in the table t1:
and the result shoul be like this:
I have mainly this two problem:
1) transpose the tab 1
2)how to execute the value of variable like instruction(code) of SAS
some of you has any idea?
Thanks.
I still can't tell whether a transpose is needed or not. At a minimum, I would need to see a third row in T1. So omitting the transpose, here is more of the detail.
data _null_;
set T1;
file 'my.translations.sas' noprint;
if _n_ > 1 then put 'else';
put 'if _n_ = ' _n_ 'then do;' ;
put 'sum_var1 = ' num ';' ;
put 'sum_var2 = ' num2 ';' ;
put 'end;' ;
run;
At this point, take a look at the file my.translations.sas to verify that it contains the right code. Then:
data want;
set t2;
%include 'my.translations.sas';
run;
The idea is that the first DATA step uses the first table to write out the code that is needed, and save it in a file. The second DATA step brings in that code, applying it to the second data set.
The piece I'm not sure about is still whether the first data set can be used as is, vs. needs to be transposed.
I was thinking about call execute as below, but it is not resolving the num wih a+b values. Any thoughts from experts would be appreciated
data t1;
input index $ num $ num2 $;
datalines;
var1 a+b b
var2 b b+c
run;
data t2;
input a b c;
datalines;
1 2 3
4 5 6
run;
proc transpose data=t1 out=test;
id index;
var num num2;
run;
data want;
merge t1(in=x) t2(in=y);
run;
data want2;
set want;
call execute('sum='||num||';');
run;
We'll need a slightly longer version of your data. It's not clear from what you posted whether it's really a requirement to transpose the first table.
One approach would be to write the instructions to a file, based on T1. For example (skipping the transpose), use T1 to write out:
if _n_=1 then do;
sum_var1 = a+b;
sum_var2 = b;
end;
else if _n_=2 then do;
sum_var1 = b;
sum_var2 = b+c;
end;
Then get the final result using:
data want;
set t2;
%include 'my.file';
run;
Ok,
Thanks for your answers,
my t1 table has 800 row(I can't put the file excel..it's private data..).. and my goal was to do everything authomatically.
Yes,.. I thought to make it with the transpose, and after, execute the value of the variable (a,a+b, sum(a,b).....) as SAS code.
I just would like to know, if there is a way to execute the value of variable as SAS base code.
yes, if there is another solution, I accept all...
thanks
I still can't tell whether a transpose is needed or not. At a minimum, I would need to see a third row in T1. So omitting the transpose, here is more of the detail.
data _null_;
set T1;
file 'my.translations.sas' noprint;
if _n_ > 1 then put 'else';
put 'if _n_ = ' _n_ 'then do;' ;
put 'sum_var1 = ' num ';' ;
put 'sum_var2 = ' num2 ';' ;
put 'end;' ;
run;
At this point, take a look at the file my.translations.sas to verify that it contains the right code. Then:
data want;
set t2;
%include 'my.translations.sas';
run;
The idea is that the first DATA step uses the first table to write out the code that is needed, and save it in a file. The second DATA step brings in that code, applying it to the second data set.
The piece I'm not sure about is still whether the first data set can be used as is, vs. needs to be transposed.
Your metadata table is confusing. You have a row with variable name of VAR1 but then you have TWO columns with formulas. Do you want to generate two ROWS so that VAR1 can have those two different values? Or do you want to make two variables and just use 'VAR1' as the prefix for the variable name? If so what names do you want for the two variables?
From the result you posted it looks like you have picked option C which is use the formulas on the diagonals. So VAR1 uses the first formula VAR2 uses the second formula, ...
data t1;
input index $ num $ num2 $;
datalines;
var1 a+b b
var2 b b+c
;
run;
data t2;
input a b c;
datalines;
1 2 3
4 5 6
;
run;
data want;
merge t2 t1;
length temp want1 want2 $ 80;
array x{*} a b c;
temp=num;
do i=1 to dim(x);
temp=compress(tranwrd(lowcase(temp),lowcase(vname(x{i})),x{i}),' ');
end;
if notdigit(strip(temp))=0 then want1=temp;
else want1=resolve(cats('%sysevalf(',strip(temp),')'));
temp=num2;
do i=1 to dim(x);
temp=compress(tranwrd(lowcase(temp),lowcase(vname(x{i})),x{i}),' ');
end;
if notdigit(strip(temp))=0 then want2=temp;
else want2=resolve(cats('%sysevalf(',strip(temp),')'));
drop i temp;
run;
Sorry,
I want to apologize...I made the mistake in the original post...and now I have modified It(the result of original post).
what I want, is to generate for every row of the table t2:
var1_of_num
var1_of_num2
var2_of_num
var2_of_num2
Sorry again, for my mistake...
with the help of the Astounding's solution I will use this sas code, that resolve the problem:
data _null_; set T1; file 'my.translations.sas' noprint; a=cats('var',_N_,'num'); b=cats('var',_N_,'num2'); put a ' = ' num ';' ; put b ' = ' num2 ';' ; run;
Thanks to everyone for help me to find the solution!!
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.