Help using Base SAS procedures

inport online excel (.xls) file

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 127
Accepted Solution

inport online excel (.xls) file

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


Accepted Solutions
Solution
‎01-21-2014 08:55 AM
Community Manager
Posts: 2,956

Re: inport online excel (.xls) file

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

Chris

View solution in original post


All Replies
Super User
Posts: 5,441

Re: inport online excel (.xls) file

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
Solution
‎01-21-2014 08:55 AM
Community Manager
Posts: 2,956

Re: inport online excel (.xls) file

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

Chris

Trusted Advisor
Posts: 2,116

Re: inport online excel (.xls) file

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

Frequent Contributor
Posts: 127

Re: inport online excel (.xls) file

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 ?

Community Manager
Posts: 2,956

Re: inport online excel (.xls) file

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

PROC Star
Posts: 7,492

Re: inport online excel (.xls) file

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

Valued Guide
Posts: 2,177

Re: inport online excel (.xls) file

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

PROC Star
Posts: 7,492

Re: inport online excel (.xls) file

: 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

Super Contributor
Posts: 387

Re: inport online excel (.xls) file

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

Attachment
Attachment
Attachment
🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

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