topic Re: SAS variable like instruction in sas in SAS Programming

SAS variable like instruction in sas

Rakeon — Sat, 22 Jul 2017 15:30:33 GMT

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.

Re: SAS variable like instruction in sas

Jagadishkatam — Sat, 22 Jul 2017 07:55:39 GMT

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;

Re: SAS variable like instruction in sas

Astounding — Sat, 22 Jul 2017 10:04:05 GMT

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;

Re: SAS variable like instruction in sas

Rakeon — Sat, 22 Jul 2017 10:59:55 GMT

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

Re: SAS variable like instruction in sas

Astounding — Sat, 22 Jul 2017 13:21:37 GMT

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.

Re: SAS variable like instruction in sas

Tom — Sat, 22 Jul 2017 14:28:51 GMT

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, ...

Re: SAS variable like instruction in sas

Ksharp — Sat, 22 Jul 2017 14:29:08 GMT

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;

Re: SAS variable like instruction in sas

Rakeon — Sat, 22 Jul 2017 15:52:45 GMT

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...

Re: SAS variable like instruction in sas

Rakeon — Sat, 22 Jul 2017 16:16:13 GMT

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!!