BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mathias
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisHemedinger
Community Manager

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

Chris

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.

View solution in original post

9 REPLIES 9
LinusH
Tourmaline | Level 20

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

Data never sleeps
ChrisHemedinger
Community Manager

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

Chris

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
Doc_Duke
Rhodochrosite | Level 12

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

mathias
Quartz | Level 8

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 ?

ChrisHemedinger
Community Manager

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

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
art297
Opal | Level 21

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

Peter_C
Rhodochrosite | Level 12

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

art297
Opal | Level 21

: 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 Smiley Happy

ScottBass
Rhodochrosite | Level 12

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


Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 4731 views
  • 3 likes
  • 7 in conversation