How to download an excel file from a password protected website

[sas_student1]

Hello, 

I have question on how to download an excel file from a password protected website using SAS. Unfortunately, I cant share the website as its password protected and confidential, but am hoping with enough detail someone can start to guide me in the right direction.

I believe this website does not have API. If I was to manually download a file, I would first have to go to a main page and type in my username and password. once in I would be at a page with multiple headers. When I click on the header a drop down comes up and I would have to click on an option "output reports" that would open another page with multiple links such as "hospital reports" or "Housing reports" , I would then click on one of those and a pop window like the pic below would come up asking "what do you want to do with [filename].xls?" I would click on save as to save the file in my directory. 

So if one was to look at the URL line I would be click through 3 URLs to get to the page I need to download the excel file from.

The transfer protocol of the URL is https, the hostname of the 3 URLs is the same, but I need to go through 3 locations of the webpage and on the third location is from where I would download the file. 

How can I do this in SAS? are there any article or sample codes that I can use and build on? Do I have to deal with cookies? If so, how? 

One more thing to note, and I have to confirm this but, I believe that the .xls file name under the "what do you want to do with" message (the black censored) is not always the same file name (which maybe annoying)

Suggestions to try would be very much appreciated. I am using SAS 9.4 TS Level 1M1. 

Thank you in advance!!

[SASKiwi]

Why not just download the Excel file manually to your PC then use SAS to read it from there? It's a whole lot easier than trying to automate a download when there is no API.

[sas_student1]

Because after downloading the file I would like to create different reports. And the data is updated monthly and eventually would Iike to automate the process.

[Kurt_Bremser]

Automation needs a stable, defined interface. From your description it is quite clear that such is not present, so you're better off doing it manually. Trying to automate that would only mean that you need to rewrite the code every time you download, and that will cause more work than doing the download yourself in the first place.

[sas_student1]

Not to be a pain but can you please explain why the interface is not stable and defined? The website link will not change, the excel table structure won’t change. Not understanding the unstability. Also just for my knowledge and getting more experience in SAS regardless of automation or why is there a way that one can do this, even if it’s complicated? I’m sure before API SAS has the capability right? Are there articles that one can point me to learn and gain knowledge?

[Kurt_Bremser]

---

@sas_student1 wrote:
Not to be a pain but can you please explain why the interface is not stable and defined? The website link will not change, the excel table structure won’t change. Not understanding the unstability. Also just for my knowledge and getting more experience in SAS regardless of automation or why is there a way that one can do this, even if it’s complicated? I’m sure before API SAS has the capability right? Are there articles that one can point me to learn and gain knowledge?

---

Quote:

"One more thing to note, and I have to confirm this but, I believe that the .xls file name under the "what do you want to do with" message (the black censored) is not always the same file name (which maybe annoying)"

So the file name changes, and it seems in unpredictable ways. This defines instability in my world.

If you can define the URL for a given file automatically, then filename url allows to specify a user and password for authentication, so you should be able to define that http access in code. See if the final URL of the file you download has a predictable structure that can be built from parameters like dates.

But if you first have to go through a login page to create a session dynamically, and then use the session key to get another page where you need to scan through the page for some text to find the next page, and then scan through that page to somehow determine today's filename, ....

You get what I am aiming at?

[sas_student1]

Ah okay I see what you are saying.
So I will check to see if the file name changes or not. But if it doesn’t change then there is a url that gets to the page where one has to click on the “hospital report” link that opens the “where do you want to save dialogue”. So manually I have to click multiple times to get to the url that has the download file. In that case I should be able to code that url and add a user and pass to the code to get the file to download right?

[sas_student1]

Something like this maybe. Only I have to do one more step before by entering a user and pass? https://blogs.sas.com/content/sgf/2018/11/01/proc-http-download-data/

[Kurt_Bremser · Accepted solution]

You can specify user and password for a URL file reference, and use that first in proc http to retrieve cookies or other session validation items.