turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- SAS variable like instruction in sas

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

07-22-2017 01:24 AM - edited 07-22-2017 11:30 AM

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.

Accepted Solutions

Solution

07-22-2017
12:06 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

07-22-2017 09:21 AM

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.

All Replies

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

07-22-2017 03:54 AM - edited 07-22-2017 03:55 AM

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

Jag

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

07-22-2017 06:04 AM

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

07-22-2017 06:55 AM - edited 07-22-2017 06:59 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

07-22-2017 09:21 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

07-22-2017 10:28 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

07-22-2017 10:29 AM

```
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;
```

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

07-22-2017 11:52 AM - edited 07-22-2017 11:52 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

07-22-2017 12:15 PM - edited 07-22-2017 12:16 PM

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