BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Rakeon
Quartz | Level 8

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.

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

8 REPLIES 8
Jagadishkatam
Amethyst | Level 16

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
Astounding
PROC Star

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;

Rakeon
Quartz | Level 8

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


 

 

Astounding
PROC Star

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.

Tom
Super User Tom
Super User

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

 

Ksharp
Super User
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;
Rakeon
Quartz | Level 8
 

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

Rakeon
Quartz | Level 8

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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