How to import HTML files which are saved in Excel files bu using sas?

[deleted_user]

Hi all,

I guess my question is very clear ...........i just want to import HTML files whuch are saved as excel format ............kindly need a solution as soon as possible....

[LinusH]

Do you mean that you have a HTML-file that you have imported to Excel, and then save as an Excel file? In this case you could good old PROC IMPORT or LIBNAME EXCEL.

/Linus

[deleted_user]

Hi Linus,

yeah exactly.................i have a HTML file which is saved as .xls in excel file.......and am trying to import those file to sas,but am getting some error.

i used proc import also,am not able to import

[LinusH]

I suggest that you share your PROC IMPORT code together with the SAS LOG, and maybe a description about how your HTML data is placed in the Excel file.

/Linus

[deleted_user]

ok i will explain clearly...........

i have a report which initially exported to excel as HTML format by using

ODS HTML Body='..................S.xls';
proc means=xxx;
.
.
.
.
..
.

ods html close;

so the file got exported to excel file as HTML frormat.


ok now i want to import this HTML format file to SAS.

Are u clear now?

kumar

[LinusH]

By doing so, I think you just created a HTML-file with a XLS suffix, but it's still a HTML-file. You need to open this file into Excel, and then Save as.. Microsoft Excel Workbook.

But why don't you just use the OUT= statement in PROC MEANS? It seems quite unnecessary complicated...

/Linus

[deleted_user]

hi.........

yeah it seems to be cpmplicated..........but this format is asked by our customer....so am doing the same.

[Cynthia_sas]

Hi:
Linus is correct. When you use ODS HTML to create an HTML file and you give that file a .XLS file extension, you are NOT exporting the file to Excel format. You are creating an HTML file that Excel knows how to open and render. Ever since Office 97, Excel has been able to open HTML files.

However, users of ODS HTML quickly learned the trick that if they named the file with a .XLS file extension, that Excel would launch from the SAS Results Window. This happens because you have fooled the Windows registry, not because you have done an export to a true, binary Excel proprietary format file.

The only mechanisms in SAS to export to Excel format are:
1) PROC EXPORT
2) Libname Excel Engine
3) SAS Add-in for Microsoft Office
and, I suppose some would say using SAS with DDE or OLE-DB. But the topic of discussion here is ODS HTML.

If you opened the HTML file (which contained output from PROC MEANS) in Excel, you would see the file in worksheet form. If you opened the HTML file with Notepad or a text editor, you would see HTML markup tags in the file.

So, if you made changes to the HTML file while in Excel and resaved it as HTML, then you might use the HTML engine to read the file back into SAS format. If you made changes to the HTML file while in Excel and resaved it as an XLS file (Office 97-2003) format, you should be able to read it with PROC IMPORT. If you made changes to the HTML file while in Excel and resaved it as an XLSX file (Office 2007 XML format), then I believe you have to use the OLE-DB engine to read the file back into SAS.

If you created the file from PROC MEANS and have not made any changes while the file has been in Excel, then Linus is correct that PROC MEANS has 2 ways to create an output data set. One way involves the OUTPUT statement inside PROC MEANS syntax and the other way involves using ODS OUTPUT to create an output data set.

But again, giving your ODS HTML file a .XLS extension is NOT doing an export to Excel format. So you must tailor your method of reading the file back into SAS (if necessary) accordingly.

cynthia

[deleted_user]

hi by the way.................what is the 3rd mechanism......i mean can u explain clearly about SAS add-in microsoft office........

[Cynthia_sas]

Hi:
Yes, I can explain what the SAS Add-In for Microsoft Office is.

The SAS Add-In for Microsoft Office is a component of the SAS Enterprise Intelligence Platform (sometimes also called the BI Platform (Business Intelligence)).

The central component of the Business Intelligence offering of SAS is the metadata server, which defines metadata about the users, servers, files, libraries, resources of the installation. Business users generally perform SAS tasks from within client applications, such as SAS Enterprise Guide, Excel, Word, SAS Web Report Studio in order to access and analyze their data -- this means that although they are using SAS on the server, they are using SAS from within an application that they are most comfortable with. For complex analyses or analysis or reporting that can best be done with SAS code (such as PROC TABULATE or SAS/Graph or PROC GLM...), the platform has the capability of allowing programmers to package SAS code into "Stored Processes" which are SAS programs that live in a stored process repository, defined to the metadata and which can be executed from the client applications that are part of the Enterprise Intelligence Platform.

The SAS Add-In for Microsoft Office is not a "stand-alone" software module that you can buy. It only comes as part of the whole Enterprise Intelligence Platform, whose purchase is generally an enterprise-level decision, which requires advance planning to configure and install.

cynthia

[deleted_user]

Hi,
thanks a lot for giving clear idea about this mechanism.

How can this be implemented and what is the procedure for doing this?
do we require a separate software for this or we can do this in sas 8 version?

if u have any material or motes please let me know the link?

regards

kumar

[LinusH]

First you need to upgrade to SAS 9.1.3. Then you need to purchase SAS BI server or some other bundle that gives you this functionality. I suggest that you contact your SAS sales representative, or having your SAS contact person to do it.

/Linus

[deleted_user]

ok thanks a lot .....will do that.

if u dont mind i have one question.........since how many years u r working on sas becoz u seems to be pretty comfortable with sas?

[Cynthia_sas]

Hi:
I have been working with SAS software for almost 30 years. I have been working at SAS for the past 12 years as an instructor and course developer.

cynthia

[deleted_user]

hi.......thats really great......ao i can learn more things from you......and my age is less than urs SAS experience....and another question.can u explain about SAS integrator with example?