topic Re: inport online excel (.xls) file in SAS Procedures

inport online excel (.xls) file

mathias — Tue, 21 Jan 2014 11:19:29 GMT

Hello

I need to import an excel file from http location (http://statbel.fgov.be/fr/binaries/311111Population%20de%20droit%201990-2011_fr_tcm326-55771.xls)

I tried proc import and data infile:

proc import

out=test

datafile='//statbel.fgov.be/fr/binaries/311111Population%20de%20droit%201990-2011_fr_tcm326-55771.xls'

dbms=excel

replace;

run;quit;

ERROR: DBMS type EXCEL not valid for import.

DATA pop1990_2011;

LENGTH

REFNIS 8

ENTITE $ 40

'1990'n 8

'1995'n 8

'2000'n 8

'2004'n 8

'2005'n 8

'2006'n 8

'2007'n 8

'2008'n 8

'2009'n 8

'2010'n 8

'2011'n 8 ;

FORMAT

REFNIS BEST12.

ENTITE $CHAR40.

'1990'n COMMA12.

'1995'n COMMA12.

'2000'n COMMA12.

'2004'n COMMA12.

'2005'n COMMA12.

'2006'n COMMA12.

'2007'n COMMA12.

'2008'n COMMA12.

'2009'n COMMA12.

'2010'n COMMA12.

'2011'n COMMA12. ;

INFORMAT

REFNIS BEST12.

ENTITE $CHAR40.

'1990'n COMMA12.

'1995'n COMMA12.

'2000'n COMMA12.

'2004'n COMMA12.

'2005'n COMMA12.

'2006'n COMMA12.

'2007'n COMMA12.

'2008'n COMMA12.

'2009'n COMMA12.

'2010'n COMMA12.

'2011'n COMMA12. ;

INFILE '//statbel.fgov.be/fr/binaries/311111Population%20de%20droit%201990-2011_fr_tcm326-55771.xls'

LRECL=144

ENCODING="WLATIN1"

TERMSTR=CRLF

DLM='7F'x

MISSOVER

DSD ;

INPUT

REFNIS : BEST5.

ENTITE : $CHAR40.

'1990'n : BEST32.

'1995'n : BEST32.

'2000'n : BEST32.

'2004'n : BEST32.

'2005'n : BEST32.

'2006'n : BEST32.

'2007'n : BEST32.

'2008'n : BEST32.

'2009'n : BEST32.

'2010'n : BEST32.

'2011'n : BEST32. ;

RUN;

ERROR: Physical file does not exist,

\\statbel.fgov.be\fr\binaries\311111Population%20de%20droit%201990-2011_fr_tcm326-55771.xls.

SAS 9.3, EG 5.1

Site number: 50600655.

Operating System: WX64_SV .

---Base SAS Software

---SAS/STAT

---SAS/GRAPH

---SAS/ETS

---SAS/CONNECT

---SAS/IntrNet

---SAS Integration Technologies

---SAS/ACCESS Interface to ODBC

---SAS Workspace Server for Local Access

---SAS Workspace Server for Enterprise Access

---DataFlux Trans DB Driver

---SAS Framework Data Server

---SAS Add-in for Microsoft Excel

---SAS Add-in for Microsoft Outlook

---SAS Add-in for Microsoft PowerPoint

---SAS Add-in for Microsoft Word

Re: inport online excel (.xls) file

LinusH — Tue, 21 Jan 2014 13:32:03 GMT

To be able to use PROC IMPORT DBMS=EXCEL you need a license for SAS/ACCESS to PC FIle Formats.

You could use SAS/ACESS to ODBC to read Excel, but I have no clue if it's possible to register a remote Excel as a datasource.

If this is something that should be done on a regular basis, you could develop a scripts that first download the Excel file, and then import it locally, seems easier...

Re: inport online excel (.xls) file

ChrisHemedinger — Tue, 21 Jan 2014 13:55:53 GMT

Without SAS/ACCESS to PC Files, you can accomplish this in a few steps:

use SAS to copy the file from the web to a path on your SAS Workspace
use SAS Enterprise Guide and the Copy Files task to download the XLS file to your local PC (not necessary if your SAS session and SAS Enterprise Guide are on the same machine)
use SAS Enterprise Guide and the Import Data task to create the SAS data set.

Chris

Re: inport online excel (.xls) file

Doc_Duke — Tue, 21 Jan 2014 14:23:04 GMT

The 'physical file does not exist' error comes from the "%20" in the file name. Replace them with spaces (" ") and SAS will be able to find the file. That still leaves the SAS/Access problem.

["%20" comes from uploading a file from windows to a *nix based file server and then downloading it to windows again. If you paste the "%20" version into a web browser, it will do the proper translation, but the direct file reference won't.]

Doc Muhlbaier

Duke

Re: inport online excel (.xls) file

mathias — Tue, 21 Jan 2014 14:58:55 GMT

Doc@Duke

INFILE '//statbel.fgov.be/fr/binaries/311111Population de droit 1990-2011_fr_tcm326-55771.xls'

ERROR: Physical file does not exist, \\statbel.fgov.be\fr\binaries\311111Population de droit

1990-2011_fr_tcm326-55771.xls.

Chris@SAS

Step1 ok

Step2 not necessary

Step3 ?

I just need to use the Import Data wizard of EG ? It's creating a "temporary text file" like 'I:\SASWork\_TD23484_SAS9XBI_\#LN00135'

Will this temporary text file be updated when the Imported XLS file is updated ?

Re: inport online excel (.xls) file

ChrisHemedinger — Tue, 21 Jan 2014 15:01:45 GMT

Yes, EG converts the XLS file into a text version that SAS will import with a DATA step.

When you re-run these steps again, the XLS file is updated with the new copy, the Import Data task recreates the text file, and the data is refreshed in your SAS session.

Learn more at:

Behind the scenes: importing Excel files using SAS Enterprise Guide - The SAS Dummy

Chris

Re: inport online excel (.xls) file

art297 — Tue, 21 Jan 2014 15:09:36 GMT

As long as you can add a step copying the file to your system's clipboard, you can also use base SAS to import the file without needing SAS/Access for PC File Formats. Take a look at: Copy and Paste from Excel to SAS - sasCommunity

Re: inport online excel (.xls) file

Peter_C — Thu, 23 Jan 2014 17:09:04 GMT

Art

having looked at your paper, I think

a small dde demo might be in order

data orsales_short;

array cols(30) $20 ;

infile clipboard device=dde notab dsd dlm='09'x truncover ;

input cols(*) ;

run ;

That successfully decoded the data from a range I expanded in excel until record length exceeded more than 256

NOTE: The infile CLIPBOARD is:

DDE Session,

SESSION=Excel|D:\Peter\Downloads\[311111Population de droit

1990-2011_fr_tcm326-55771.xls]fr!R2C1:R12C40,

RECFM=V,LRECL=32767

NOTE: 11 records were read from the infile CLIPBOARD.

The minimum record length was 203.

The maximum record length was 387.

NOTE: The data set WORK.ORSALES_SHORT has 11 observations and 30 variables.

All cells were loaded in a reasonable way (although I have what I defined - just strings).

So perhaps the lack of support for LRECL= needn't become a problem

However I regret using dde.

It is so deprecated 😉

regards

Peter

Re: inport online excel (.xls) file

art297 — Thu, 23 Jan 2014 17:52:30 GMT

: With DDE one can use LRECL

filename clippy dde 'clipboard';

data want;

infile clippy lrecl=400 dsd notab missover dlm='09'x firstobs=2;

informat long_field $char327.;

informat quarter yyq6.;

format quarter YYQ6.;

informat product_group $char24.;

input quarter product_group quantity profit long_field;

run;

filename clippy clear;

It may be deprecated, but sure can still come in handy

Re: inport online excel (.xls) file

ScottBass — Sat, 25 Jan 2014 01:48:54 GMT

Yet another approach: use Powershell to run a query against the Excel file, output as CSV, then read as CSV either via unnamed pipe, temp file and data step, or temp file and PROC IMPORT DBMS=csv (which just creates a data step anyway, but is still handy when the file structure changes).

See attached file for a Powershell script that runs an SQL query against Excel. If you're interested further I can provide supporting SAS code.

Hope this helps,

Scott