how to scrape data from a webpage

[Anita_n]

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_=2...

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.

[Kurt_Bremser · Accepted solution]

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;

[Anita_n]

@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

[Kurt_Bremser]

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.

[Anita_n]

@Kurt_Bremser : okay, thankyou

[Kurt_Bremser]

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

[Anita_n]

okay