topic Re: Inserting the value of a variable into a text string in SAS Programming

Inserting the value of a variable into a text string

Wolverine — Mon, 20 Mar 2023 18:03:37 GMT

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;

Re: Inserting the value of a variable into a text string

Wolverine — Mon, 20 Mar 2023 18:05:13 GMT

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

Re: Inserting the value of a variable into a text string

russt_sas — Mon, 20 Mar 2023 18:26:20 GMT

Are you wanting SAS to create something like this:

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

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

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

...

Re: Inserting the value of a variable into a text string

Wolverine — Mon, 20 Mar 2023 18:27:24 GMT

Yes, so it will apply that formula for each row

Re: Inserting the value of a variable into a text string

russt_sas — Mon, 20 Mar 2023 18:30:25 GMT

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;

Re: Inserting the value of a variable into a text string

Wolverine — Mon, 20 Mar 2023 18:58:52 GMT

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

Re: Inserting the value of a variable into a text string

russt_sas — Mon, 20 Mar 2023 19:53:49 GMT

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

Re: Inserting the value of a variable into a text string

Wolverine — Mon, 20 Mar 2023 20:00:35 GMT

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.

Re: Inserting the value of a variable into a text string

russt_sas — Mon, 20 Mar 2023 20:25:32 GMT

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

Re: Inserting the value of a variable into a text string

Tom — Mon, 20 Mar 2023 20:59:12 GMT

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?

Re: Inserting the value of a variable into a text string

Wolverine — Mon, 20 Mar 2023 20:59:02 GMT

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;

Re: Inserting the value of a variable into a text string

Wolverine — Mon, 20 Mar 2023 21:15:34 GMT

@Tom wrote:

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?

Yes, it should look like a formula that Excel could use. Another team will use a series of these Excel files (more than 70 of them) to manually audit various groups of records for agreement -- 1 is there is agreement between our the 2 data sources, and 0 if there is not. The Excel file has columns is this type of order: Variable1, Score1, Variable2, Score2, etc. The audit team has requested that I put the formula in place at the end of the row to save them a lot of copying, pasting, and auto-filling down the column.

Re: Inserting the value of a variable into a text string

Tom — Mon, 20 Mar 2023 21:31:03 GMT

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

Re: Inserting the value of a variable into a text string

Tom — Mon, 20 Mar 2023 21:34:12 GMT

Why not just put the actual sum instead?

data want;
  set have;
  total = sum(of score:);
run;

Re: Inserting the value of a variable into a text string

Wolverine — Tue, 21 Mar 2023 13:52:55 GMT

@Tom wrote:

Why not just put the actual sum instead?
data want;
  set have;
  total = sum(of score:);
run;

Because the score variables are blank in SAS. The audit team will fill in the score fields in Excel as they do their comparison. So the idea is that the formula will already be present at the end of each row and will automatically calculate the row score.

Re: Inserting the value of a variable into a text string

Wolverine — Tue, 21 Mar 2023 14:05:11 GMT

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

Re: Inserting the value of a variable into a text string

Tom — Tue, 21 Mar 2023 14:16:22 GMT

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/highlight/true#M325599

Re: Inserting the value of a variable into a text string

Wolverine — Tue, 21 Mar 2023 14:42:19 GMT

@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/highlight/true#M325599

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

Re: Inserting the value of a variable into a text string

Cynthia_sas — Tue, 21 Mar 2023 15:17:47 GMT

HI:

Have you thought about specifying the formula using TAGATTR? For example, in the code below, the EX1 workbook uses the data values for ACTUAL and PREDICT to calculate DIFF. But if I change any of those values in the worksheet after opening, the value for DIFF changes as soon as the value for either ACTUAL or PREDICT changes.

However, as shown in EX2, if I use computed items that are initially set to 0 (called C_ACT and C_PRED), then the columns are initially 0 when I open the output:

But as soon as I update the columns with values, the DIFF column changes:

The code below shows both examples, but the screen shots are from the workbook created in EX2 code.


  ods excel file='c:\temp\formula_ex1.xlsx';

  proc report data=sashelp.prdsale spanrows;
     where country eq 'CANADA' and year eq 1993;
     column country prodtype product actual predict diff;
	 define country / group;
	 define prodtype / group;
	 define product / group;
     define predict / sum style(column)={tagattr='format:Currency'};
     define actual / sum style(column)={tagattr='format:Currency'};
     define diff / computed 
            style(column)={width=1in tagattr='format:Currency formula:RC[-1]-RC[-2]'};
     compute diff;
	   diff=0;
	 endcomp;
  run;
  ods excel close;

  
  ods excel file='c:\temp\formula_ex2.xlsx';

  proc report data=sashelp.prdsale spanrows;
     where country eq 'CANADA' and year eq 1993;
     column country prodtype product c_act c_pred diff;
	 define country / group;
	 define prodtype / group;
	 define product / group;
     define c_pred / computed style(column)={tagattr='format:Currency'};
     define c_act / computed style(column)={tagattr='format:Currency'};
	 compute c_pred;
	   c_pred = 0;
	 endcomp;
	 compute c_act;
	   c_act = 0;
	 endcomp;
     define diff / computed 
            style(column)={width=1in tagattr='format:Currency formula:RC[-1]-RC[-2]'};
     compute diff;
	   diff=0;
	 endcomp;
  run;
  ods excel close;

Using TAGATTR is an alternative approach.

Cynthia

Re: Inserting the value of a variable into a text string

Tom — Tue, 21 Mar 2023 16:43:14 GMT

The issue seems to be with the extremely long string required by the formula. I cannot figure out if there is a way to prevent the length from causing the formula to be seen as text by Excel.

Perhaps using this FORMULA tag will work instead, that does not seem to be impacted by the use of a very long formula.

So assuming that the original request to sum every other column from column 23 to 73 could be recoded as meaning to sum every other relative cell from -1 to -51 then perhaps something like this will work.

    define total_score / computed 
            style(column)={width=1in tagattr='formula:RC[-1]+RC[-3]+RC[-5]+RC[-7]+RC[-9]+RC[-11]+RC[-13]+RC[-15]+RC[-17]+RC[-19]+RC[-21]+RC[-23]+RC[-25]+RC[-27]+RC[-29]+RC[-31]+RC[-33]+
RC[-35]+RC[-37]+RC[-39]+RC[-41]+RC[-43]+RC[-45]+RC[-47]+RC[-49]+RC[-51]'};
     compute diff;