turn on suggestions

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

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- ODS and Base Reporting
- /
- Creating a Vlookup formula in MVS and creating a e...

Topic Options

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-22-2008 09:55 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to deleted_user

01-22-2008 11:17 AM

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]

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]

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Cynthia_sas

01-22-2008 11:55 AM

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)

this is what the formula looks like

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to deleted_user

01-22-2008 03:44 PM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Cynthia_sas

01-23-2008 07:17 AM

Thank you