proc import xlsx from url

[andrewjmdata]

Hi, I have tried all sorts of code combinations to import the following...

https://fingertips.phe.org.uk/documents/Historic COVID-19 Dashboard Data.xlsx

This code being one combination....

filename xlsxFile http "https://fingertips.phe.org.uk/documents/Historic COVID-19 Dashboard Data.xlsx" ;

proc import file=xlsxfile

  out=phe_ftip

  dbms=xlsx replace;

  sheet="UTLAs";

  GUESSINGROWS=1000000;

run;

The error I get is:

ERROR: Input file https:\\fingertips.phe.org.uk\documents\Historic COVID-19 Dashboard Data.xlsx specified for DATAFILE= could not be found

I can download the file manually so am confused as to where I am going wrong.

Any help appreciated.

Cheers

Andrew

[ghosh]

does not look like the file exists, I was not able to download it manually. are you sure the file name is correct?

[andrewjmdata]

Thanks for your reply Ghosh. 

I just copied all the text between the quotes and put it into Chrome address window and it found the file and opened in Excel.

If you just clicked on the link, agreed, that doesn't work.

Andrew

[ghosh]

you need to escape the spaces

https://fingertips.phe.org.uk/documents/Historic%20COVID-19%20Dashboard%20Data.xlsx

[andrewjmdata]

ERROR: Input file
https:\\fingertips.phe.org.uk\documents\Historic%20COVID-19%20Dashboard%20Data.xlsx
specified for DATAFILE= could not be found

😞

[ghosh]

try libname, since the filename approach is not supported 

 1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 70         
 71         filename xlsxFile http "https://fingertips.phe.org.uk/documents/Historic%20COVID-19%20Dashboard%20Data.xlsx" ;
 72         
 73         
 74         
 75         proc import file=xlsxfile
 76         
 77           out=phe_ftip
 78         
 79           dbms=xlsx replace;
 ERROR: This "filename URL" access method is not supported by "proc import". Please copy the file to local disk before running the 
        procedure.
 
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: PROCEDURE IMPORT used (Total process time):

[ghosh]

BTW the syntax uses url not http, as below.   Also, the data begins in row 8, your code needs to account for this

filename xlsxFile url "https://fingertips.phe.org.uk/documents/Historic%20COVID-19%20Dashboard%20Data.xlsx" ;

[andrewjmdata]

Tried url and http .. same outcome, can't actually find the file.. odd!

[andrewjmdata]

Tried this:

libname xl XLSX 'https://fingertips.phe.org.uk/documents/Historic%20COVID-19%20Dashboard%20Data.xlsx';

/* discover member (DATA) names */
proc datasets lib=xl;
run;

Result:

13920 libname xl XLSX 'https://fingertips.phe.org.uk/documents/Historic%20COVID-19%20Dashboa
13920 ! rd%20Data.xlsx';
NOTE: Library xl assigned as follows:
Engine: XLSX
Physical Name: C:\Users\spurf\Documents\WPS
Workspaces\Workspace1\https:\\fingertips.phe.org.uk\documents\Historic%20COVID-19%20Dashbo
ard%20Data.xlsx

13921
13922 /* discover member (DATA) names */
13923 proc datasets lib=xl;
NOTE: No matching members in directory
13924 run;
NOTE: Procedure datasets step took :
real time : 0.034
cpu time : 0.015

Does code work on your environment? I am using WPS but I don't think that's an issue.

Thanks!

[Tom]

Two things. 

1) URL engine does not like the spaces in the URL. Replace them with %20.

2) PROC IMPORT does not like URL engine. Copy the file to a physical file first.

%let url=https://fingertips.phe.org.uk/documents/Historic%20COVID-19%20Dashboard%20Data.xlsx;
filename src url "&url" recfm=f lrecl=512;
filename dest temp recfm=f lrecl=512;
data _null_;
   length msg $ 384;
   rc=fcopy('src', 'dest');
   if rc=0 then
      put 'Copied SRC to DEST.';
   else do;
      msg=sysmsg();
      put rc= msg=;
   end;
run;
proc import datafile=dest dbms=xlsx
  out=want replace
;
run;

168   proc import datafile=dest dbms=xlsx
169     out=want replace
170   ;
171   run;

NOTE:    Variable Name Change.  UK COVID-19 Cases, Deaths and Re -> UK_COVID_19_Cases__Deaths_and_Re
NOTE: One or more variables were converted because the data type is not supported by the V9 engine. For
      more details, run with options MSGLEVEL=I.
NOTE: The import data set has 6 observations and 5 variables.
NOTE: WORK.WANT data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.03 seconds
      cpu time            0.01 seconds

[andrewjmdata]

Hi Tom, Looks like that works for you.

Am getting an error saying "ERROR: The function "fcopy" is not known" I suspect that might be because I am using WPS.

I realise this is SAS forum so shouldn't really ask for help here on that one 😉

I might even see if I can create a DOS routine to download the file.

Thanks all.

Andrew

[ghosh]

I just added two lines to Tom's code in the proc import and it works.  Except since the variable names are dates (numbers not allowed as var names) so you have to massage values in row 1 to make them into variable names

proc import datafile=dest dbms=xlsx
  out=want replace
;
 sheet="UTLAs";
datarow=8;
run;

[Tom · Accepted solution]

You should be able to just use the data step to copy the file.

filename src url "&url" recfm=f lrecl=512;
filename dest temp recfm=f lrecl=512;
data _null_;
  infile src;
  file dest;
  input;
  put _infile_;
run;

[andrewjmdata]

Bingo! 

With the proc import Ghosh has that now works pretty well.

Thanks both.

[ChrisNZ]

The function FCOPY should be used here.