topic Re: How to import HTML files which are saved in Excel files bu using sas? in SAS Procedures

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

deleted_user — Mon, 01 Dec 2008 09:59:19 GMT

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

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

LinusH — Mon, 01 Dec 2008 11:38:18 GMT

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

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

deleted_user — Mon, 01 Dec 2008 12:37:02 GMT

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

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

LinusH — Mon, 01 Dec 2008 12:46:25 GMT

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

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

deleted_user — Mon, 01 Dec 2008 12:55:53 GMT

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

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

LinusH — Mon, 01 Dec 2008 13:46:47 GMT

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

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

Cynthia_sas — Mon, 01 Dec 2008 13:56:23 GMT

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

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

deleted_user — Mon, 01 Dec 2008 13:59:09 GMT

hi.........

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

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

deleted_user — Mon, 01 Dec 2008 14:01:11 GMT

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

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

Cynthia_sas — Mon, 01 Dec 2008 14:12:14 GMT

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

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

LinusH — Mon, 01 Dec 2008 14:14:10 GMT

That is a part of SAS BI Server package. You need to install SAS components with your MS Office client installation. It means that you can use Excel as a client to connect to SAS via Intelligent Architecture Servers. You can access data directly or running Stored Processes, among other thing. See www.sas.com and support.sas.com for more extensive information about this topic.

/Linus

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

deleted_user — Mon, 01 Dec 2008 14:17:24 GMT

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

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

LinusH — Mon, 01 Dec 2008 14:24:45 GMT

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

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

Cynthia_sas — Mon, 01 Dec 2008 14:25:21 GMT

Yes, this is all separate client-server software. I do not think the BI platform will work with SAS 8. It was first introduced with SAS 9.

If you go to www.sas.com and investigate some of these links:
http://www.sas.com/technologies/bi/
http://www.sas.com/technologies/bi/entbiserver/index.html
http://www.sas.com/resources/factsheet/sas-enterprise-bi-server-factsheet.pdf

it may give you a better idea about enterprise-level software of this magnitude.

cynthia

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

deleted_user — Mon, 01 Dec 2008 14:29:08 GMT

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?

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

deleted_user — Mon, 01 Dec 2008 14:30:10 GMT

oh thanks a lot.

kumar

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

Cynthia_sas — Mon, 01 Dec 2008 14:33:47 GMT

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

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

deleted_user — Mon, 01 Dec 2008 14:37:56 GMT

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?

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

Cynthia_sas — Mon, 01 Dec 2008 17:26:04 GMT

Hi:
I really don't know what you mean by "sas integrator". Perhaps you mean SAS Integration Technologies? You are better off searching support.sas.com and the documentation for examples at support.sas.com.

For example, here are some Tech Support notes and documentation links which may be of interest to you (which were found by using the SEARCH engine on support.sas.com and searching on phrases like Integration Technologies web services .NET .COM etc):
http://www.mysas.net/fI8Bx1pMa5/inttech/library/index.html
http://support.sas.com/documentation/onlinedoc/inttech/
http://support.sas.com/rnd/itech/doc9/dev_guide/websrvcs/using.html
http://support.sas.com/rnd/itech/doc9/dev_guide/websrvcs/index.html
http://support.sas.com/rnd/itech/doc9/dev_guide/websrvcs/decide.html
http://support.sas.com/rnd/itech/doc9/dev_guide/websrvcs/program.html
http://support.sas.com/rnd/itech/updates/912/portal_dev.pdf
http://www.eclipse.org/community/casestudies/SASfinal.pdf
http://www.sas.com/technologies/bi/query_reporting/guide/customtasks.html
http://support.sas.com/kb/7/568.html
http://support.sas.com/kb/7/770.html
http://support.sas.com/kb/7/567.html
http://support.sas.com/kb/25/286.html
http://support.sas.com/kb/25/272.html
http://support.sas.com/documentation/tools/oledb/index.htm
http://www2.sas.com/proceedings/sugi31/231-31.pdf
http://www2.sas.com/proceedings/sugi29/037-29.pdf
http://support.sas.com/rnd/appdev/V30/doc/sugi29p24-29.pdf

If you don't feel entirely comfortable searching support.sas.com, a Google search can also net you many SAS Global Forum papers on SAS topics like this or basic overviews of SAS functionaliry. You could also search the books by users at the SAS publication site, where you might find this link to a book by Dr. Pratter about Web Development that includes a discussion of Integration Technologies.
http://www.sas.com/apps/pubscat/bookdetails.jsp?catid=1&pc=59836

At any rate, there is a wealth of information and examples already available at the support.sas.com web site and if you have specific coding or installation or configuration issues, then SAS Tech Support can help you resolve those problems.

cynthia

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

deleted_user — Tue, 02 Dec 2008 08:21:53 GMT

hi synthia,

thanka a lot for providing valuable information.Yeas sas integrator means integrating sas with other softwares like java.dot net etc