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
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;
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
you need to escape the spaces
https://fingertips.phe.org.uk/documents/Historic%20COVID-19%20Dashboard%20Data.xlsx
ERROR: Input file
https:\\fingertips.phe.org.uk\documents\Historic%20COVID-19%20Dashboard%20Data.xlsx
specified for DATAFILE= could not be found
😞
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):
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" ;
Tried url and http .. same outcome, can't actually find the file.. odd!
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!
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
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
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;
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;
Bingo!
With the proc import Ghosh has that now works pretty well.
Thanks both.
The function FCOPY should be used here.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.