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
Without SAS/ACCESS to PC Files, you can accomplish this in a few steps:
Chris
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...
Without SAS/ACCESS to PC Files, you can accomplish this in a few steps:
Chris
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
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.
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 ?
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
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
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
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
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: