topic Re: proc import xlsx from url in SAS Programming

proc import xlsx from url

andrewjmdata — Mon, 30 Mar 2020 14:59:50 GMT

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

Re: proc import xlsx from url

ghosh — Mon, 30 Mar 2020 15:10:47 GMT

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

Re: proc import xlsx from url

andrewjmdata — Mon, 30 Mar 2020 17:17:08 GMT

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

Re: proc import xlsx from url

ghosh — Mon, 30 Mar 2020 17:19:54 GMT

you need to escape the spaces

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

Re: proc import xlsx from url

andrewjmdata — Mon, 30 Mar 2020 17:24:26 GMT

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

😞

Re: proc import xlsx from url

ghosh — Mon, 30 Mar 2020 17:45:47 GMT

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):

Re: proc import xlsx from url

ghosh — Mon, 30 Mar 2020 18:02:37 GMT

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" ;

Re: proc import xlsx from url

andrewjmdata — Mon, 30 Mar 2020 18:04:29 GMT

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!

Re: proc import xlsx from url

Tom — Mon, 30 Mar 2020 18:07:58 GMT

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

Re: proc import xlsx from url

andrewjmdata — Mon, 30 Mar 2020 18:08:14 GMT

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

Re: proc import xlsx from url

andrewjmdata — Mon, 30 Mar 2020 18:15:19 GMT

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

Re: proc import xlsx from url

ghosh — Mon, 30 Mar 2020 18:28:05 GMT

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;

Re: proc import xlsx from url

Tom — Mon, 30 Mar 2020 18:36:49 GMT

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;

Re: proc import xlsx from url

andrewjmdata — Mon, 30 Mar 2020 18:44:53 GMT

Bingo!

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

Thanks both.

Re: proc import xlsx from url

ChrisNZ — Tue, 31 Mar 2020 03:20:27 GMT

The function FCOPY should be used here.

Re: proc import xlsx from url

andrewjmdata — Tue, 31 Mar 2020 06:12:16 GMT

Sorry, I don't understand.

Whatever the code does do, I do have an output file as a SAS table which looks correct.

Re: proc import xlsx from url

ChrisNZ — Tue, 31 Mar 2020 10:58:38 GMT

Something like

filename SRC url "&url" recfm=f lrecl=512; filename DEST temp recfm=f lrecl=512; %let rc = %sysfunc(fcopy(SRC,DEST));

Re: proc import xlsx from url

Tom — Tue, 31 Mar 2020 16:43:19 GMT

@ChrisNZ wrote:

The function FCOPY should be used here.

@ChrisNZ Please read the whole thread.

Re: proc import xlsx from url

ChrisNZ — Tue, 31 Mar 2020 20:26:22 GMT

Oh, I see. WPS. And fcopy was mentioned. Apologies for my hasty comment then. 😓