Import xlsx file into SAS from static url link

user_11929169 0
Viewed 1000

I am new to SAS but used to working in python.

In python, I can read an xlsx file from a static Box.com link by calling pandas read excel function on the Box link.

pd.read_excel("https://box.com/shared/static/<url>.xlsx")

I'm hoping to do something similar in SAS. When I try

PROC IMPORT DATAFILE="https://box.com/shared/static/<url>.xlsx"
        OUT=WORK.MYEXCEL
        DBMS=XLSX
        REPLACE;
RUN;

SAS tries to look for a document with the name "https://box.com/shared/static/.xlsx".

When I try

filename xlsxFile http "https://box.com/shared/static/<url>.xlsx";

PROC IMPORT DATAFILE=xlsxFile
        OUT=WORK.MYEXCEL
        DBMS=XLSX
        REPLACE;
RUN;

I get

ERROR: This "filename URL" access method is not supported by "proc import". Please copy the file to local disk before running the 
       procedure.

Is there an easy way to have SAS access files from this type of URL? I've checked these few threads: https://communities.sas.com/t5/General-SAS-Programming/import-excel-file-from-the-web/td-p/134158 https://communities.sas.com/t5/SAS-Programming/Importing-XLSX-from-URL-Issue/td-p/446758 https://communities.sas.com/t5/SAS-Programming/proc-import-xlsx-from-url/td-p/635834

And it seems like I may need to do some work to get SAS to create a file object from the URL but I don't quite understand how the code is working and when I naively try something similar:

filename xlsxFile http "https://box.com/shared/static/<url>.xlsx";

data file;
   n=-1;
   infile xlsxFile recfm=s nbyte=n length=len;
   input;
   file  "file_name.xlsx" recfm=n;
   put _infile_ $varying32767. len;
run;

PROC IMPORT OUT= input DATAFILE= "file_name.xlsx" 
     DBMS=xls REPLACE;
     SHEET="sheet_name";
     GETNAMES=YES;
RUN;

I get the following error:

Spreadsheet isn't from Excel V5 or later.  Please open it in Excel and Save as V5 or later
Requested Input File Is Invalid
ERROR: Import unsuccessful.  See SAS Log for details.

Any help would be appreciated. The reason I'd like to do it this way is because the files at the static links are updated daily and I want to avoid having to copy files to the SAS server every day. So if this won't work I am also interested in other work arounds that would accomplish the same thing.

I can obviously write a script that will fetch the updated files and write them to the server where SAS can access them as needed, but wanted to see if I could get this to work first.

Using the Box API from within SAS would also be another option if anyone has successfully gotten that to work. As I mentioned I am new to SAS so trying to access the API seemed like it would be too difficult at the moment.

Thank you!

sas
1 Answers

You'll definitely need to download the file. As Tom notes in comments, make sure to check this is really an xlsx file; but assuming it is, some suggestions for dealing with it.

Copying it over by hand like you're doing is doable, and I'll put some code that works at the bottom of the answer, but it's way overkill nowadays - you're probably reading decade-old papers from before the modern day of SAS. In particular, you should use PROC HTTP to GET the file, rather than using the URL directly - it's just much easier that way.

Another possibility is you could use python for this! Regardless of your SAS setup, you can use python to script SAS, or use SAS to run Python, very easily nowadays. SASPy project (on github) for SAS 9, or the SAS SWAT package (also on github I believe) for connecting to SAS Viya, lets you very easily talk back and forth with Python and SAS, even in production - I do it all the time. And on top of that, you can even write SAS user-written functions in python now - so there're a bunch of ways to get your Python into SAS, if you want. I am a SAS (primary) developer, but I use Python for web-related stuff, since it's just better at it. See my paper for more details, or lots of other resources on the subject.

Assuming you just want to import the file and don't want to keep track of the excel file ultimately, you can just read it from a temp filename, which will clean itself up afterwards:

filename _httpin temp;

proc http method="get" 
  url="https://github.com/snoopy369/SASL/raw/master/Excel%20Precision.xlsx"
  out=_httpin;
run;

proc import file=_httpin out=test dbms=xlsx replace;
run;

If you want the excel file saved somewhere (sounds like you don't, but if you do), then instead of temp assign that httpin filename to a real location.

filename _httpin "c:\temp\whatever.xlsx";

If you really want to do that binary file copy business, do it this way:

filename _httpin temp;
filename _httpout "c:\temp\Excel Precision.xlsx";

proc http method="get" 
  url="https://github.com/snoopy369/SASL/raw/master/Excel%20Precision.xlsx"
  out=_httpin;
run;

data _null_;
  _in = fopen('_httpin','I',1,'B');
  _out= fopen('_httpout','O',1,'B');
  rec='20'x;
  do while (Fread(_in) eq 0);
    rc = fget(_in,rec,1);
    rc = fput(_out,rec);
    rc = fwrite(_out);
  end;
  rc = fclose(_in);
  rc = fclose(_out);
run;


proc import file=_httpout out=test dbms=xlsx replace;
run;
user_16230070
Related