Creating a Vlookup formula in MVS and creating a excel file using ODS tagse

deleted_user — Tue, 22 Jan 2008 14:55:02 GMT

I am creating an excel file using ods in MVS. I am trying to create a variable with a vlookup formula in it so when I open it up in excel it will pull from another excel file automatically. when I print the data set before outputting it using ods It looks like the vlookup formula is in the variable however when I open the file up in excel I get a table error (the file will open) but there is no formula the variable is blank.

Re: Creating a Vlookup formula in MVS and creating a excel file using ODS tagse

Cynthia_sas — Tue, 22 Jan 2008 16:17:28 GMT

Hi:
When you use ODS, you are creating one of these types of files that can be opened with Excel:
1) ODS CSV (creates a TXT file that has comma separated values)
2) ODS HTML-based destinations (ODS HTML3, ODS HTML, ODS MSOFFICE2K) creates an HTML file that can be opened with Excel
3) ODS TAGSETS.ExcelXP (creates a Spreadsheet Markup Language XML file that conforms to the Microsoft spec for Office 2002 and higher).

I only know of the ability to supply a formula with ODS TAGSETS.EXCELXP -- so here's what I would check:
1) is your syntax for ExcelXP correct for specifying the formula?
2) are you using the most current version of the ExcelXP tagset?
3) are you using Excel 2002 or higher -- it was my memory, that if you had Excel 2000, that you'd just see the XML in the file. If you're getting a Table error, then you probably have Excel 2002 or higher.

To check #1, I don't know the syntax for a VLOOKUP formula. But I have successfully used a formula with TAGSETS.EXCELXP -- see the program below. If you can get this program to work in your environment, then to me, that indicates that the formula you're using is not quite correct. (Of course, you'd have to adjust the FILE= option for the mainframe and you'd have to FTP the results to a platform with Excel 2002 or higher to open the XML)

To check #2, look in the SAS log, you should see a note when you use TAGSETS.EXCELXP that says:
[pre]
NOTE: This is the Excel XP tagset (SAS 9.1.3, v1.70, 06/05/07).
[/pre]

As far as I know, setting a formula did work in earlier versions of ExcelXP, but this is the version I used to create my output file and when I opened the file, the values in the PROFIT column were calculated by Excel. I always reverse engineer how a formula looks, by creating a dummy spreadsheet, making an example of the formula I need, then saving the file as XML and opening the file with Notepad to see what the formula looks like. I don't normally think in R/C notation -- which is how I remember the formula has to be specified.

If you are sure that your VLOOKUP formula is correct and you are using the most current version of the ExcelXP tagset, then your best bet for help is to contact Tech Support, as they have both Microsoft and ExcelXP expertise.

cynthia

[pre]
data tkt_alt;
input sample dest $ type $ amt ;
** profit needed for ExcelXP formula example;
** set var to missing in SAS -- want excel to calc formula;
profit = .;
return;
datalines;
0010 CHICAGO TEL 100
0222 CHICAGO TEL 200
0330 GENEVA WEB 300
0040 GENEVA WEB 400
0550 LONDON TEL 500
0066 LONDON TEL 600
0777 LONDON WEB 700
0088 PARIS TEL 800
0990 PARIS WEB 900
;
run;

ods tagsets.excelxp file='c:\temp\tagattr_formula.xls'
style=sasweb;
proc report data=tkt_alt nowd ;
title 'Using tagattr and TAGSETS.EXCELXP';
title2 'Send a Format and Formula for a Column';
column dest sample amt profit;
define dest/display;
define sample /display
style(column)={tagattr="00000"};
define amt/ sum
style(column)={tagattr="$#,####.00"};
define profit / sum "Profit"
style(column)=
{tagattr='Formula:=SUM(RC[-1]*0.9)'};
run;

** Profit will be AMT * .9 and AMT is 1 column to the LEFT of PROFIT;
** in Excel -- so RC[-1] is how I point to AMT;
ods tagsets.excelxp close;

[/pre]

Re: Creating a Vlookup formula in MVS and creating a excel file using ODS tagse

deleted_user — Tue, 22 Jan 2008 16:55:44 GMT

Cynthia, we are using the tagset excelxp version that you have listed. I am created my formulas as variable before I output using tagset excelxp.

this is what the formula looks like

=vlookup($15,'Processed_Pro_Report.csv'!$B$2:$AG$100,31,False)

Re: Creating a Vlookup formula in MVS and creating a excel file using ODS tagse

Cynthia_sas — Tue, 22 Jan 2008 20:44:41 GMT

Tracy:

I've never tried passing a formula as a variable value. I thought the only way you could pass a formula via Tagsets.ExcelXP was as the value for the TAGATTR style element. You'd have to check with Tech Support to be sure.

cynthia

Re: Creating a Vlookup formula in MVS and creating a excel file using ODS tagse

deleted_user — Wed, 23 Jan 2008 12:17:12 GMT

Thank you

topic Re: Creating a Vlookup formula in MVS and creating a excel file using ODS tagse in ODS and Base Reporting

Creating a Vlookup formula in MVS and creating a excel file using ODS tagse

Re: Creating a Vlookup formula in MVS and creating a excel file using ODS tagse

Re: Creating a Vlookup formula in MVS and creating a excel file using ODS tagse

Re: Creating a Vlookup formula in MVS and creating a excel file using ODS tagse

Re: Creating a Vlookup formula in MVS and creating a excel file using ODS tagse