Inserting the value of a variable into a text string

[Wolverine]

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;

[Wolverine]

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

[russt_sas]

 Are you wanting SAS to create something like this:

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

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

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

...

[Wolverine]

Yes, so it will apply that formula for each row

[russt_sas]

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;

[Wolverine]

---

@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)

[russt_sas]

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

[Wolverine]

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.

[russt_sas]

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

[Wolverine]

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;

[Tom]

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

[Wolverine]

---

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

[Tom]

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?

https://communities.sas.com/t5/SAS-Programming/Writing-Excel-formulas-in-SAS-using-ods/m-p/824430/hi...

[Wolverine]

---

@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?

https://communities.sas.com/t5/SAS-Programming/Writing-Excel-formulas-in-SAS-using-ods/m-p/824430/hi...

---

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');

[Tom]

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?