Import .XSL file into SAS from a website

Accepted Solution Solved
Reply
Contributor
Posts: 32
Accepted Solution

Import .XSL file into SAS from a website

Hello

I would like to know if there is a way to Import an .XSL file to SAS from a website.  I tried to come up with a solution but I always receive an error - Physical file does not exist.

Thanks in advance.
Daniel


Accepted Solutions
Solution
‎09-19-2014 03:10 AM
Contributor
Posts: 32

Re: Import .XSL file into SAS from a website

I tried executing your code but nothing happens, nothing is saved.

My log log:

 

Owner Name=rsasjob,Group Name=rsas,

Access Permission=rw-rw-r--,

Last Modified=Fri Sep 19 09:03:06 2014

NOTE: 9 records were written to the file DUMMY1.

The minimum record length was 11.

The maximum record length was 98.

NOTE: DATA statement used (Total process time):

real time 0.00 seconds

cpu time 0.02 seconds

 

NOTE: DATA statement used (Total process time):

real time 0.07 seconds

cpu time 0.00 seconds

View solution in original post


All Replies
PROC Star
Posts: 7,474

Re: Import .XSL file into SAS from a website

Do you just want to use it to convert an XML file? If so, how about: Base SAS(R) 9.2 Procedures Guide?

Contributor
Posts: 32

Re: Import .XSL file into SAS from a website

Sorry, it was a typo.. Smiley Happy I meant .XLS not .XSL. I want to import an Excel file to SAS and I want to import it from a website.

Super User
Super User
Posts: 7,955

Re: Import .XSL file into SAS from a website

Download the file to a local area then proc import or infile.  Or do you mean you need to download a file at routine timepoints e.g. monthly?  If so then your probably best off scripting that in an FTP software e.g Filezilla. 

Contributor
Posts: 32

Re: Import .XSL file into SAS from a website

Exactly, I would like to download it at routine timepoints. I know that there are other options but is there a way to do it as I want it to? Write a code that would import it into SAS dataset and also filter it?

Super User
Super User
Posts: 7,955

Re: Import .XSL file into SAS from a website

Your talking about two differing things there.  The first task is to download and store the file itself.  IMO this is probably (though am open to suggestions - proc http only takes text data as far as I remember) best done in a specific way outside of SAS -> e.g. consider the questions "is the file complete", "is the file not corrupted" plus some other considerations.

The second question is can you import and filter the file.  This is easy, yes, proc import or libname to the xls when it is local and then post-process the imported dataset.

Contributor
Posts: 32

Re: Import .XSL file into SAS from a website

Well, I've seen some threads about the use of proc http to import .XLS files into SAS dataset from web-based sources, for some people it worked and for some it didn't. I'm among those that receive an error - "Physical file does not exist". I tried executing something like this:

filename input TEMP;

proc http method="get"

url="http://site.com/file.xls"

out=input;

run;

proc cimport file= input lib=work;

run;

As for proc import, I have exactly the same problem.. I can't import an .XLS even from a folder on a hard drive. When I choose EXCEL as an DBMS I get "ERROR: DBMS type EXCEL not valid for import", but when I change it to XLS - "Physical file does not exist". And yes, I do have SAS/ACCESS to PC Files. So am I doing something wrong?

SAS Super FREQ
Posts: 3,753

Re: Import .XSL file into SAS from a website

PROC Star
Posts: 7,474

Re: Import .XSL file into SAS from a website

I, too, haven't had any success using proc http to import excel files from the web. Once you get the file, I think you want to import it into SAS using proc import.

If you are on windows here is an alternative to proc http:

%macro importxl(webfile,outfile);

  data _null_;

    length script filevar $256;

    script = catx('\',pathname('WORK'),'PasteIt.vbs');

    filevar = script;

    script="'"||'cscript "'||trim(script)||'"'||"'";

    call symput('script',script);

    file dummy1 filevar=filevar recfm=v lrecl=512;

  

    put 'Set objExcel = CreateObject("Excel.Application")';

    put 'Dim objExcel';

    put 'Dim Newbook';

    script=catt('Set Newbook = objExcel.Workbooks.Open("',"&webfile.",'")');

    put script;

    put 'objExcel.DisplayAlerts = False';

    script=catt('Newbook.SaveAs("',"&outfile.",'")');

    put script;

    put 'objExcel.Workbooks.Close';

    put 'objExcel.DisplayAlerts = True';

    put 'objExcel.Quit';

  run;

  data _null_;

    call system(&script.);

  run;

%mend importxl;

%importxl(webfile=http://spreadsheetpage.com/downloads/xl/climate_data.xls/,

          outfile=c:\art\testexcel.xls)

In the above example call to the macro it imports a 2-sheet workbook that actually does exist on the web and saves it to a file (testexcel.xls) in my c:\art directory.

Conversely. if you're not on Windows, the following will accomplish the same task:

FILENAME website HTTP

'http://spreadsheetpage.com/downloads/xl/climate_data.xls/';

data file;

  n=-1;

  infile website recfm=s nbyte=n length=len;

  input;

  file  "c:\art\exceltest.xls" recfm=n;

  put _infile_ $varying32767. len;

run;

Solution
‎09-19-2014 03:10 AM
Contributor
Posts: 32

Re: Import .XSL file into SAS from a website

I tried executing your code but nothing happens, nothing is saved.

My log log:

 

Owner Name=rsasjob,Group Name=rsas,

Access Permission=rw-rw-r--,

Last Modified=Fri Sep 19 09:03:06 2014

NOTE: 9 records were written to the file DUMMY1.

The minimum record length was 11.

The maximum record length was 98.

NOTE: DATA statement used (Total process time):

real time 0.00 seconds

cpu time 0.02 seconds

 

NOTE: DATA statement used (Total process time):

real time 0.07 seconds

cpu time 0.00 seconds

Trusted Advisor
Posts: 3,214

Re: Import .XSL file into SAS from a website

Donitik, there are two technical questions hidden in your single request.

1/ downloading data from the internet.

2/ converting a excel xls file to SAS data.

You are on a Unix alike system as I see owner group and permissions being mentioned.  You are telling you are having SAS/access pc files but not what SAS version you are on.

ad 2/

xls files are old pre 2007 style Excel files. You are needing the Windows ACE driver for that. That one is not available for Unix. You could have a pcfiles-server being active and a Windows up/downlaod for that.

Using a xlsx file you can use that type of Excel on Unix as it Open-Office a like a zipped construct of some XML files.

SAS/ACCESS(R) 9.4 Interface to PC Files: Reference, Third Edition (Microsoft Excel Workbook Files)

What's New in SAS(R) 9.4 (reading/writing xlsx in Unix is a new feature)

 

ad 1/

The download from data to a SAS environment is well explained by Rick reference


It should all be possible in the SAS UE as that one is running SAS 9.4.

ArthurT approach is doing a scripted start of Excel with some commands. I have not seen MS-office on Unix or I have missed something.

---->-- ja karman --<-----
Contributor
Posts: 38

Re: Import .XSL file into SAS from a website

Hi,

Open blank Excel and go to Data tab.

From Other Sources select from Web.

Copy Website Address and paste into Address at top , click Go

Check Excel file you want to link to and click Import.

Save this Excel file

Now you can import the excel data from Web into your SAS program.

Fred

PROC Star
Posts: 7,474

Re: Import .XSL file into SAS from a website

I agree with . I suggested two different sets of code.  The first one was ONLY if you were on windows. Since it appears that you aren't on Windows, the 2nd set should work on all systems.

If it does, and you have Excel on your computer, you can then open the file and save it as an xlxs file.

Contributor
Posts: 32

Re: Import .XSL file into SAS from a website

The thing is that I'm on windows. I don't use UNIX. My SAS version is 9.2. So I have no idea what is going on because nothing works.. As for the second one, there's an error - "ERROR: SSL Error: Missing CA trust list.

ERROR: Insufficient authorization to access"

Trusted Advisor
Posts: 3,214

Re: Import .XSL file into SAS from a website

If you do not use Unix, where is this coming from in your log:

   Owner Name=rsasjob,Group Name=rsas, Access Permission=rw-rw-r--,

These are Unix related messages not Windows. Are you using Eguide connected to a server?

Than you are EGuide (.Net) using on Windows and the server with all SAS processes are on he server-side. That one can be Unix.

"ERROR: SSL Error: Missing CA trust list.  ERROR: Insufficient authorization to access"  Google that and you find: Encryption in SAS(R) 9.2 . If you connect something external and needing an encrypted protocol this step must be done. In windows the suppliers like Microsoft google are delivering this by default.  

What are you using? (fully descriptions/components)  

---->-- ja karman --<-----
🔒 This topic is solved and locked.

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

Discussion stats
  • 14 replies
  • 609 views
  • 0 likes
  • 6 in conversation