How to import a CSV file from a user and password protected website.

[sas_student1]

Hello, 

I have a question on how one can import a csv file from a website that is user and password protected. I have access to this website. Via the website a reports are run on data that is collected. Each of the reports have an option to export the data under the report as a csv. Now I can go to each report and manually click and download the csv file (its ; delimited BTW), but I was wondering if there is a way to have SAS access the site (I would need to put in the HTML and user and password information but how?) and also have it direct to the report (which I have noticed has its own unique html) and then export the csv. But how can I do this?

You assistance would be great!

Thanks!!

[ballardw]

I would start here to have the documentation for Proc Http https://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#a003286672.htm  Proc Http is a way to pass browser actions/requests and get the results.

Then read this as it works through an example similar to what I think your are asking:

https://blogs.sas.com/content/sgf/2018/11/01/proc-http-download-data/

And some results from searching for: "Proc HTTP" CSV That may have bits of interest.

https://blogs.sas.com/content/sasdummy/2017/05/07/download-convert-csv-files/

https://www.sas.com/content/dam/SAS/support/en/sas-global-forum-proceedings/2019/3232-2019.pdf

Getting the pieces may take more than one try as the second link shows.

[sas_student1]

@ballardw Thank you!! This is really helpful. So I was able to proc http into the website. had to use webusername and webpassword. Got no errors, yay!!! 

But the issue. In the articles and blog that you sent the http website went directly to the .csv file. For example in github to get the raw data there is a .csv file. The issue I have is that when I click on the button of the report that says "CSV Export" it automatically creates the CSV file. Now I tried to right click on the button thinking maybe an http address can be saved, but no luck. What I get are the items in the screen shot below. I figure that maybe either "view page source" or "inspect" may have a clue but I am not sure how to read it. other suggestions? I feel like I am almost there but I think I am not using the right link?

. 

[ballardw]

If you have gotten that far then you would use a data step code to READ the data from that link. If you have a manual down loaded previous file then write data step to read that. If you have been relying on Proc Import or similar then you could use that one time and capture the code generated, save and modify. Then use the Proc HTTP result as the fileref on the INFILE statement in the data step.

Note about "import" using the Proc Import or wizards: Each file is processed separately. That means variable types and lengths can change from import to import based on the content and options used on proc import. Additionally some sorts of values such as Account or Identification numbers that may have leading zeroes will be read as numeric and lose significant leading zeroes. These should be character variables but Import won't recognize them as such.

In the manual import I suggest above to get data you want to set GUESSINGROWS=max; so more of the file is examined.

Or better yet, the source of the file should have some documentation of the contents so that you can modify the created data step code to conform as to length and variable type.

Additionally, data step will allow you deal with types of data that should be numeric but may have text codes like N/A, Missing,

Not recorded, "< 5" or similar by creating custom informats that will treat the text into a desired numeric or missing value.

[Tom]

See if you can tell what the button is supposed to do by looking at the source code for the page. 

If it is not a simple link to a specific file then perhaps it is a form that you need to submit that returns the CSV file.  PROC HTTP should be able to handle that also using the POST method.