Usage Note 23080: How to read HTML tables into SAS
The following code can be used to import HTML tables into SAS. It uses macro code, Dynamic Data Exchange (DDE), and the IMPORT procedure to read the HTML table, and it is limited to 65,000 observations.
**************************************************************;
* htmlread.sas *;
* Make sure you read all comments in the program fully !!!! *;
* Purpose to be able to read HTML files into the SAS system *;
* It can also create a .xls file as a bonus. *;
* HTML is limited to 65,000 observations that Excel can do. *;
* SAS/Access to PC file formats is required to make this run.*;
* *;
* Filen is the name of the HTML file you wish to read *;
* into the SAS system. *;
* Filedir is the name of the directory of the HTML file *;
* Filedir2 is the name of the directory you wish to save *;
* the .xls file. By default the .xls file is the same *;
* name as the HTML file. *;
* Row1 is a counter saying whether you have variable names *;
* in the first row of the HTML table you are trying to read. *;
* Sasname is the name of the SAS dataset you are creating. *;
**************************************************************;
%macro htmlread(filen,filedir,filedir2,row1,sasname);
options mprint macrogen symbolgen;
options noxwait noxsync;
data setup;
%let fullname=&filedir&filen;
%let savefile=&filedir2&filen..xls;
%let del1=call system(%unquote(%str(%'del "&savefile"%')));
run; quit;
data setup2;
&del1;
run; quit;
/* Change the path to the install location for Excel on your machine */
x 'c:\progra~1\micros~2\office10\excel.exe';
%put &fullname;
%let opener=%str(%'[open("&fullname")]%');
%let saveas=%str(%'[save.as("&savefile",1)]%');
/* The following DATA step gives Excel two seconds to start */
data _null_;
x=sleep(2);
run;
quit;
filename cmds dde 'excel|system';
data _null_;
file cmds;
put %unquote(&opener);
/*----------------------------------------------------------------------------------------------------*/
/* Delete the title and the top three rows */
/* You will need to modify this program as needed to remove extraneous info */
/* from your HTML file */
/* For more information about these commands, see the macrofun.exe Help file */
/* You can get a copy of the macrofun.exe from www.microsoft.com */
/*----------------------------------------------------------------------------------------------------*/
put '[select("r1:r3")]';
put '[edit.delete(3)]';
* Now save the file as an excel file *;
put %unquote(&saveas);
put '[quit()]';
run;
* Now import the file as a SAS dataset from Excel *;
proc import datafile="&savefile"
out=&sasname replace;
sheet="&filen";
getnames=&row1;
run; quit;
/*----------------------------------------------------------------------------------------*/
/* Uncomment the following lines if you want the Excel file */
/* to disappear after the program is finished */
/*----------------------------------------------------------------------------------------*/
*data setup2;
*&del1;
*run;
*quit;
%mend htmlread;
%htmlread(print2,c:\testsas2\,c:\mysas,yes,sasuser.test1);
Operating System and Release Information
*
For software releases that are not yet generally available, the Fixed
Release is the software release in which the problem is planned to be
fixed.
Type: | Usage Note |
Priority: | low |
Date Modified: | 2012-10-01 15:58:14 |
Date Created: | 2002-12-16 10:56:48 |