topic Re: Using API for CSV data in SAS Data Science

Using API for CSV data

postollma — Sat, 02 Feb 2019 21:38:10 GMT

I am trying to download data that is in a CSV format from a query on the project tycho website.

I have the following:

filename response temp;

proc http
url="https://www.tycho.pitt.edu/api/query?apikey=MYAPIKEYHERE&ConditionName=Measles&CountryISO=US"
out=response;
run;

I have seen answers to other similar questions that show how to do the next step (libname statement) with JSON formats, but not with CSV. Does anyone know how to do this?

Also, this is the first time I have worked with APIs. After the CSV is imported, do I just continue to analyze the data with data steps and procedures? Thanks!

Re: Using API for CSV data

Patrick — Sun, 03 Feb 2019 04:42:26 GMT

Your URL must either be in single quotes or within %NRSTR() for SAS not interpreting the & as SAS macro tokens.

filename response temp;
proc http
  url=%nrstr("https://www.tycho.pitt.edu/api/query?apikey=MYAPIKEYHERE&ConditionName=Measles&CountryISO=US")
  out=response;
run;

FILENAME RESPONSE TEMP;

will write whatever you download into a file on disk stored in the SAS WORK location. The file is temporary in the sense that the WORK location gets deleted when terminating a SAS session.

If you know that what you're downloading is a .csv then you could for development purposes write this file to another location than SAS WORK. To do so you simply need to change your filename statement to something like:

FILENAME RESPONSE "<folder path accessible to where SAS executes>\myfile.csv";

Now you can easily access the file and inspect its content. Next step will be to read the external data into SAS. It it's a .csv the either use PROC IMPORT or even better use a SAS Datastep.

What I'm normally doing with text files like a .csv when SAS EG is available:

1. download the file to my local disk

2. use the SAS import wizard to generate the import code (but you can also use the code Proc Import generates)

3. modify the code to my final solution (i.e. change lengths and informats)

Once done with development point your filename back to TEMP.

...and if you just want to quickly see in the log what you're downloading then code as below should do:

options ls=max ps=max;
filename response temp lrecl=100000;
proc http
  url=%nrstr("https://www.tycho.pitt.edu/api/query?apikey=MYAPIKEYHERE&ConditionName=Measles&CountryISO=US")
  out=response;
run;
data _null_;
  infile response;
  input;
  put _infile_;
run;

Re: Using API for CSV data

postollma — Tue, 05 Feb 2019 18:01:36 GMT

Thanks!

I tried using single quotes and the %nrstr() options, but the file doesn't show up in the work folder.

I also tried saving to my documents folder instead of temp, but the file doesn't show up there either.

Any thoughts on what is going wrong?

Re: Using API for CSV data

Patrick — Wed, 06 Feb 2019 03:31:39 GMT

@postollma

If I run the previously posted code then a file gets created with content:

Invalid API key. Please see the <a href="/dataset/api">API Help</a> (https://www.tycho.pitt.edu/dataset/api) for more information.

Do you get the same when running the code with the invalid API key? And what happens if you use a valid API key? AND: What does the SAS log tell you?

Re: Using API for CSV data

postollma — Tue, 19 Feb 2019 15:12:43 GMT

@Patrick Try this url with my APIKEY

https://www.tycho.pitt.edu/api/query?apikey=056b7aa4c98a061c4136&ConditionName=Measles&CountryISO=US

After I have this run, the log says "PROCEDURE HTTP used" then gives me the real and cpu processing times.

It also has a note that says "200 OK"

Re: Using API for CSV data

Patrick — Tue, 19 Feb 2019 20:13:58 GMT

@postollma

Sorry for answering so late. Below code works for me.

options ls=max ps=max;
filename response temp lrecl=100000;
proc http
  url='https://www.tycho.pitt.edu/api/query?apikey=056b7aa4c98a061c4136&ConditionName=Measles&CountryISO=US'
  out=response;
run;
data _null_;
  infile response;
  input;
  put _infile_;
run;