topic Re: how to scrape data from a webpage in SAS Programming

how to scrape data from a webpage

Anita_n — Wed, 10 Nov 2021 07:58:38 GMT

Dear all,

I have a question. I need a code to export data (xlsx) from this website to sas.

(please note that the site is in german)

https://www.destatis.de/SiteGlobals/Forms/Suche/Expertensuche_Formular.html?resourceId=2402&input_=2408&pageLocale=de&templateQueryString=Alle+politisch+selbst%C3%A4ndigen+Gemeinden+mit+ausgew%C3%A4hlten+Merkmalen+&submit.x=0&submit.y=0

Please could any one help ?

I need probably a macro to download all the excel files for all subsequent years (I will like to download sheet2, the first sheet is not required). I saw a blog which posted this code

filename src temp;
proc http
 method="GET"
 url="https://www.destatis.de/SiteGlobals/Forms/Suche/Expertensuche_Formular.html?resourceId=2402&input_=2408&pageLocale=de&templateQueryString=Alle+politisch+selbst%C3%A4ndigen+Gemeinden+mit+ausgew%C3%A4hlten+Merkmalen+&submit.x=0&submit.y=0"
 out=src;
run;

which I tried to use, but it seems am not doing the right thing.

I will appreciate any help.

Re: how to scrape data from a webpage

Kurt_Bremser — Wed, 10 Nov 2021 08:50:59 GMT

I copied the URL from the download link, and ran this:

filename ht temp;

proc http
  url="https://www.destatis.de/DE/Themen/Laender-Regionen/Regionales/Gemeindeverzeichnis/Administrativ/Archiv/GVAuszugJ/31122020_Auszug_GV.xlsx;jsessionid=B4477D957BFAB0E007E62A86373ED7F9.live711?__blob=publicationFile"
  out=ht
;
run;

proc import
  datafile=ht
  out=test
  dbms=xlsx
  replace
;
sheet="Onlineprodukt_Gemeinden";
run;

Re: how to scrape data from a webpage

Anita_n — Wed, 10 Nov 2021 09:34:14 GMT

@Kurt_Bremser Thanks for that. The only problem is that the columnnames are not read in properly. Is there anyway to state that these variablenames should be used as the columnnames? Satzart, Textkennzeichen, land, RB, Kreis, VB, Gem, Gemeinde, Fläche as Flaeche, insgesamt, männlich as maennlich, je km as km, Längengrad as Laengengrad, Breitengrad

Re: how to scrape data from a webpage

Kurt_Bremser — Wed, 10 Nov 2021 10:01:39 GMT

The problem here is that these spreadsheets have obviously not been created with further machine processing in mind. Some column "names" are in row 3, others in row 4. You will have to do a lot of work in a follow-up data step to set your intended column names manually (either by renaming, or by creating new variables). PROC IMPORT will result with all columns as character, so you must also convert to numeric where needed.

Re: how to scrape data from a webpage

Anita_n — Wed, 10 Nov 2021 10:19:12 GMT

@Kurt_Bremser : okay, thankyou

Re: how to scrape data from a webpage

Kurt_Bremser — Wed, 10 Nov 2021 10:49:46 GMT

Depending on your environment, you might be able to convert the xlsx to csv by using VBA with Office (Windows), or LibreOffice (Linux) from the commandline. The csv will then be much easier to read in a data step where you have full control over everything (column names and types, start of data, etc).

Re: how to scrape data from a webpage

Anita_n — Wed, 10 Nov 2021 11:54:18 GMT

okay