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)
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.
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;
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;
@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
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.
@Kurt_Bremser : okay, thankyou
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).
okay
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 16. 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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: