Download Excel from Internet

The ability to read external data such as Excel direclty from the web can eliminate manual processes. SAS can do this but it is not intuitive. Fortunately, I found this post by Louis Sinoff on the SAS Support Communities web site. I am using a land area Excel file available on the Census bureau web site as the sample data. The purpose of this post is to provide some details on how this works.

The URL access method creates a reference to the Excel file on the web. The INFILE statement sets the RECFM = N or as a binary byte stream. NBTYE = N points back the variable N which was assigned a value of -1. NBYTE set to -1 will return the number of bytes available to the input buffer. Of course, the LENGTH = LEN assignment is used with the VARYING format.

Once downloaded, use the EXCEL data engine to read the Excel file contents in SAS. Because there is a special character ($) used in the data set name, you need to quote the name and use the 'N or name literal.

filename webfile url "http://www2.census.gov/prod2/statcomp/usac/excel/LND01.xls" ;
filename outexcel "c:\temp\x.xls" ;
data _null_ ;
  n = -1 ;
  infile webfile 
    recfm  = n 
    nbyte  = n  
    length = len ;
  file outexcel 
    recfm  = n ;
  input ;
  put _infile_ $varying32767. len ;
run;
filename webfile clear ;
filename outexcel clear ;

libname xldata excel "c:\temp\x.xls" ;

data mydata ;
  set xldata.'sheet1$'n( obs = 5 ) ;
run ;