topic Re: Downloading data from web page in SAS Programming

Downloading data from web page

AlexeyS — Tue, 23 Jul 2019 05:11:43 GMT

Hello, I need help. I want to download data from the next url : eurocontrol

How can I do it? Thank you

Re: Downloading data from web page

andreas_lds — Tue, 23 Jul 2019 05:17:52 GMT

Contact the site, they will have set up a service you can use maybe by using proc soap or proc http, but that depends on what they offer.

Re: Downloading data from web page

Kurt_Bremser — Tue, 23 Jul 2019 07:50:32 GMT

Try this:

filename in url "https://www.eurocontrol.int/sites/default/files/2019-07/ur-2019-07-update.txt";

data want;
infile in dlm='09'x dsd;
input
  code :$2.
  date1 :yymmdd10.
  date2 :yymmdd10.
  number1
  number2
  code2 :$3.
  name :$40.
;
run;

Note that this will only work if you have the proper certificate chain on your SAS server so that https works.

Re: Downloading data from web page

AlexeyS — Tue, 23 Jul 2019 09:05:30 GMT

Thank you very much. Your code is really work. But when I try to read the first or second month, I got an error.

https://www.eurocontrol.int/sites/default/files/2019-06/ur-2019-01.txt

https://www.eurocontrol.int/sites/default/files/2019-06/ur-2019-02.txt

As I see, the problem is with number2 in your example.

As I understand, it can be two different delimiters, sometimes tab delimiter, and sometimes blank delimiter.

Re: Downloading data from web page

Kurt_Bremser — Tue, 23 Jul 2019 09:01:16 GMT

That's because they use a dot for decimals in the first file, and a comma in the second. A typical violation of the structural "agreement".

If that change was not documented, I would alert the source to that. If it is documented, you will have to adapt the code so that it can deal with it:

data want;
infile in dlm='09'x dsd;
input
  code :$2.
  date1 :yymmdd10.
  date2 :yymmdd10.
  number1
  _number2 :$10.
  code2 :$3.
  name :$40.
;
if indexc(_number2,',')
then number2 = input(_number2,commax10.);
else number2 = input(_number2,10.);
drop _number2;
run;

Re: Downloading data from web page

AlexeyS — Tue, 23 Jul 2019 09:45:02 GMT

You are right. But still I have a problem, when I try to read month 1 and 2.

You can see, that code2 variable in some cases inside name variable.

I tried multiple delimiters, like

dlm=' ' || '09'x;

dlm=dlm,

but it doesn't work me

https://www.eurocontrol.int/sites/default/files/2019-06/ur-2019-01.txt

https://www.eurocontrol.int/sites/default/files/2019-06/ur-2019-02.txt

Re: Downloading data from web page

Kurt_Bremser — Tue, 23 Jul 2019 10:58:30 GMT

That's because the file is not properly structured. In line 6 of 2019-01, there's a blank separating number2 and code2 instead of a tab. The people there clearly need to get their act together. If everything else is built as sloppily as this, I would not trust this data at all. Who knows what other errors are hidden in there.

Note that we can't use blanks as additional delimiters, as name often contains blanks.

PS 2019-02 looks much better, so it may be that the problems were corrected from there on.

Re: Downloading data from web page

Ksharp — Tue, 23 Jul 2019 11:58:14 GMT

filename in url "https://www.eurocontrol.int/sites/default/files/2019-07/ur-2019-07-update.txt";
proc import datafile=in out=want dbms=tab replace;
run;

Re: Downloading data from web page

AlexeyS — Tue, 23 Jul 2019 12:52:41 GMT

Thank you very much!