Inserting the value of a variable into a text string

[Wolverine]

---

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

[Tom]

Why not just put the actual sum instead?

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

[Wolverine]

---

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

[Cynthia_sas]

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

[Tom]

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;

[Wolverine]

---

@Tom wrote:

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;

---

Yes, the issue does seem to be the length of the formula.  I experimented and found that I could add no more than 8 columns before Excel started displaying the formula instead of calculating it.  So I created a work-around by putting portions of the formula in columns further out to the right, and then summing those columns.  I used the absolute_column_width setting in the ODS Excel options to give these extra columns a width of 0 so they are not visible.  It was tedious, but it works.

/*Maximum of 8 columns, or else Excel will display formula rather than value*/  
  formula1='=W3+Y3+AA3+AC3+AE3+AG3+AI3+AK3'; 
  formula2='=AM3+AO3+AQ3+AS3+AU3+AW3+AY3+BA3'; 
  formula3='=BC3+BE3+BG3+BI3+BK3+BM3+BO3+BQ3'; 
  formula4='=BS3+BU3'; 
  formula_comb='=BX3+BY3+BZ3+CA3';

Excelformula1=tranwrd(formula1,'3',cats(_n_+2)); /*Enter in Column BX*/
Excelformula2=tranwrd(formula2,'3',cats(_n_+2)); /*Enter in Column BY*/
Excelformula3=tranwrd(formula3,'3',cats(_n_+2)); /*Enter in Column BZ*/
Excelformula4=tranwrd(formula4,'3',cats(_n_+2)); /*Enter in Column CA*/

Excelformula_comb=tranwrd(formula_comb,'3',cats(_n_+2)); 
	row_score = cats(Excelformula_comb,'*(20/26)'); /*Enter in Column BW*/

I didn't try the tagattr approach because I would basically have to start over, learning and programming a new approach.  My deadline is too close to get into that.  Maybe for next year's audit...