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

Showing results for

- Home
- /
- Programming
- /
- Programming
- /
- Inserting the value of a variable into a text string

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 03-20-2023 02:03 PM
(554 views)

I'm trying to insert a formula into an Excel spreadsheet that will be created by SAS. There are 2 header rows in the Excel file, so I have to adjust the row number accordingly. The below code works as a proof of concept, but unfortunately the actual formula is more complex. When I try to create a formula with multiple columns, I get errors or the end result actually includes the text "row_number_adj" instead of the actual row number.

The actual formula (as it should appear in Excel) is

=(W3+Y3)*(20/26)")

where 3 should be replaced by the value of row_number_adj

```
DATA want; SET have;
row_number = _N_;
row_number_adj = row_number + 2;
Excelformula = COMPRESS("=W"||row_number_adj);
row_score = cats(Excelformula);
RUN;
```

20 REPLIES 20

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Sorry, the formula in Excel should be =(W3+Y3)*(20/26)

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Are you wanting SAS to create something like this:

=(W3+Y3)*(20/26)

=(W4+Y4)*(20/26)

=(W5+Y5)*(20/26)

...

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Yes, so it will apply that formula for each row

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Something like this:

DATA want; SET have;

row_number = _N_;

row_number_adj = row_number + 2;

Excelformula = COMPRESS("=(W"||row_number_adj||'+'||'Y'||row_number_adj||')');

row_score = cats(Excelformula,'*(20/26)');

RUN;

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

@russt_sas wrote:

Something like this:

DATA want; SET have;

row_number = _N_;

row_number_adj = row_number + 2;

Excelformula = COMPRESS("=(W"||row_number_adj||'+'||'Y'||row_number_adj||')');

row_score = cats(Excelformula,'*(20/26)');

RUN;

That's really really close, but for some reason it's inserting a line return after the = sign. So it displays in Excel as

=

(W3+Y3)*(20/26)

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

How are you sending the SAS data set to excel for viewing?

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

With ODS Excel and Proc Report. I also noticed that the character fields in Excel have text wrapping on by default. That could explain why there is a break between the = and the W. On the other hand, manually turning off the text wrap in Excel does NOT make the formula calculate.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Can you attach the exact code that is not working for you?

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

I'm trying to use a longer version of the formula:

```
DATA want; SET havet;
DROP row_number row_number_adj Excelformula;
row_number = _N_;
row_number_adj = row_number + 2;
Excelformula = COMPRESS("=(W"||row_number_adj||'+'||'Y'||row_number_adj||'+'||'AA'||row_number_adj||'+'||'AC'||row_number_adj||'+'||'AE'||row_number_adj||'+'||'AG'||row_number_adj||'+'||'AI'||row_number_adj||'+'||'AK'||row_number_adj||'+'||'AM'||row_number_adj||'+'||'AO'||row_number_adj||'+'||'AQ'||row_number_adj||'+'||'AS'||row_number_adj||'+'||'AU'||row_number_adj||'+'||'AW'||row_number_adj||'+'||'AY'||row_number_adj||'+'||'BA'||row_number_adj||'+'||'BC'||row_number_adj||'+'||'BE'||row_number_adj||'+'||'BG'||row_number_adj||'+'||'BI'||row_number_adj||'+'||'BK'||row_number_adj||'+'||'BM'||row_number_adj||'+'||'BO'||row_number_adj||'+'||'BQ'||row_number_adj||'+'||'BS'||row_number_adj||'+'||'BU'||row_number_adj||')');
row_score = cats(Excelformula,'*(20/26)');
RUN;
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Why are you using COMPRESS()? You seem to already know about CATS(), so why not just use it?

But why not just use TRANWRD() instead?

```
length formula $200 ;
formula='=W3+Y3+AA3+AC3+AE3+AG3+AI3+AK3+AM3+AO3+AQ3+AS3+AU3+AW3+AY3+BA3+BC3+BE3+BG3+BI3+BK3+BM3+BO3+BQ3+BS3+BU3';
formula=tranwrd(formula,'3',cats(_n_+2));
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

@Tom wrote:

Why are you using COMPRESS()? You seem to already know about CATS(), so why not just use it?

But why not just use TRANWRD() instead?

`length formula $200 ; formula='=W3+Y3+AA3+AC3+AE3+AG3+AI3+AK3+AM3+AO3+AQ3+AS3+AU3+AW3+AY3+BA3+BC3+BE3+BG3+BI3+BK3+BM3+BO3+BQ3+BS3+BU3'; formula=tranwrd(formula,'3',cats(_n_+2));`

This works and is the simplest way I've seen to do it, but Excel is seeing it as a text string rather that a formula. In other words, the cell displays this:

=W3+Y3+AA3+AC3+AE3+AG3+AI3+AK3+AM3+AO3

+AQ3+AS3+AU3+AW3+AY3+BA3+BC3+BE3+BG3

+BI3+BK3+BM3+BO3+BQ3+BS3+BU3*(20/26)

If I double-click on the cell and then press enter, it will calculate properly. But that's a lot of extra clicking for the audit team. Unfortunately, this seems like an Excel issue rather than a SAS issue.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

So you have now moved onto your actual problem.

How are you creating the EXCEL file from the dataset?

What options did you try to tell SAS that it should treat the value of that variable as Excel CODE instead of TEXT?

Did you try the formulas='on' option to ODS EXCEL?

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

@Tom wrote:

So you have now moved onto your actual problem.

How are you creating the EXCEL file from the dataset?

What options did you try to tell SAS that it should treat the value of that variable as Excel CODE instead of TEXT?

Did you try the formulas='on' option to ODS EXCEL?

I did not have the formulas option turned on, so that got my hopes up... unfortunately it's still doing the same thing 😞 Here is my current ODS Excel command:

```
ods excel options(sheet_name='Scoring Sheet' sheet_interval='now' frozen_headers='on' formulas='on');
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

You want to create a character variable that has a string that looks like an formula you could type into Excel?

```
data want;
set have;
string=cats('=(w',_n_+2,'+y',_n_+2,')*(20/26)');
run;
```

What do you plan to do with this formula? Are you going to try and write it into an Excel spreadsheet so that it will actually perform calculations? Why?

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

**If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. **

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.