Proc Http - The quoted string currently being processed has become more than 262 characters long

[LL5]

I want to use Proc http to download a xlsb file from a website, then read the data and manipulate it. 

I have never used Proc http before and have two questions. Any advices would be greatly appreciated. 

First Question - my URL address is very long. I tried two different approaches as below, but both are getting the same warning message as following. How should I proceed with this? 

NOTE: The quoted string currently being processed has become more than 262 characters long. You might have unbalanced quotation

     marks.

1st approach - quote the URL directly in proc http 

filename out TEMP;
filename hdrs TEMP;

proc http url="http://sharesites.shareddrive.abcdeft.net/sites/tog-tops-001/abcdegmeeting/abcdegmeeting/_layouts/do... Dashboards/ABCD Info Abcdefg Dashboard.xlsb"
method="GET"
out=out
headerout=hdrs;
run;

2nd approach - try to break it into different parts and make it as a macro variable 

DATA _NULL_;
LENGTH URL $20000;
URL=CATS('http://sharesites.shareddrive.abcdeft.net/sites/tog-tops-001/abcdegmeeting/abcdegmeeting/_layouts/do...'
,'abcdegmeeting/Data%20and%20Reports/'
,'Abcdefg Dashboards/ABCD Info Abcdefg Dashboard.xlsb');
call symputx('URL',quote(trim(URL)," ' "));
run;

PROC HTTP URL=&URL
METHOD="GET"
OUT=out;
RUN;

My second question - assume my first question is solved and I could successfully run the procedure http, then how do I read the dataset? In this case, I would like to make 'ABCD Info Abcdefg Dashboard.xlsb' as a SAS dataset and mainpulate it. 

Thanks for the advices. 

[Tom]

If your quoted string is actually longer than 262 then you can safely ignore that note.  Unbalanced quotes is a common mistake and SAS added that warning to help people figure out what was going on.

If your file is really using XLSB format instead of XLSX then you might have trouble reading it from SAS.  You might have to get Excel involved to help read that format.

[LL5]

Thanks Tom. Is it because SAS cannot read XLSB file? A separate question, assume that is a XLSX file, how would I read it as a SAS dataset? By assignment a libname with XLSX engine and specifying a fileref? 

[Tom]

Yes. But you have to physically copy the file to the machine where SAS is running.  You cannot make the XLSX libname engine work via the URL filename engine.  So if you copied the file to your OUT temporary file then point the new libref at that file.

libname HDRS xlsx "%sysfunc(pathname(out))" ;
proc copy inlib=hdrs outlib=WORK;
run;

[LL5]

Thanks Tom again. I am trying an alternative approach which is download the file from web site, save it in my desktop and move the file to SAS server under my user ID (using FTP). However, I am still getting error as below. I wonder if you could give me further advices?

PROC IMPORT OUT=test1

DATAFILE= "/sharedspace/abcd/users/a123456/Abcd_xyz_file_Update__yyyymmdd.xlsb"

DBMS=EXCEL REPLACE;

sheet="Abc data Query Final";

RUN;

ERROR: DBMS type EXCEL not valid for import.

I looked up the SAS document as below and it says DBMS identifier = EXCEL could support MS Excel 2010 and later workbooks version. Since I am using MS excel 2016, I thought it should work.

https://documentation.sas.com/?docsetId=acpcref&docsetTarget=p0jf3o1i67m044n1j0kz51ifhpvs.htm&docset...

[Tom]

Does your SAS server run Windows? Does it have Excel installed? If not do you a SAS PC Files Server you can tell your code to connect to and see if it can convert the XLSXB file?

[LL5]

My SAS server is on Unix which I believe it does not support window/Excel. What I use to do often is use FTP to transfer excel or csv file from window to SAS server and save it under my SAS user folder, it works all the times. Now I assume it does not work for xlsb only but I couldn't find any document to support my assumption.