DATA Step, Macro, Functions and more

SAS variable like instruction in sas

Accepted Solution Solved
Reply
Contributor
Posts: 49
Accepted Solution

SAS variable like instruction in sas

[ Edited ]

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:

Immagine.png

 

Now I have another table:

data t2;
 input a b c;
datalines;
1 2 3
4 5 6
run;

like this:

Immagine.png

 

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

 

Immagine.png

 


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.

 

 

 


Accepted Solutions
Solution
‎07-22-2017 12:06 PM
Super User
Posts: 5,516

Re: SAS variable like instruction in sas

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.

View solution in original post


All Replies
Trusted Advisor
Posts: 1,137

Re: SAS variable like instruction in sas

[ Edited ]

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;

 

Thanks,
Jag
Super User
Posts: 5,516

Re: SAS variable like instruction in sas

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;

Contributor
Posts: 49

Re: SAS variable like instruction in sas

[ Edited ]
Posted in reply to Astounding

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


 

 

Solution
‎07-22-2017 12:06 PM
Super User
Posts: 5,516

Re: SAS variable like instruction in sas

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.

Super User
Super User
Posts: 7,076

Re: SAS variable like instruction in sas

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

 

Super User
Posts: 10,044

Re: SAS variable like instruction in sas

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;
Contributor
Posts: 49

Re: SAS variable like instruction in sas

[ Edited ]
 

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

Contributor
Posts: 49

Re: SAS variable like instruction in sas

[ Edited ]

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 317 views
  • 4 likes
  • 5 in conversation