topic Re: Extracting Data from JSON File from API in SAS Programming

Extracting Data from JSON File from API

A_SAS_Man — Mon, 17 Jun 2019 18:29:17 GMT

I am using the following code to access an API and retrieve some data:

filename resp 'location/demo.json';

proc http
method="GET"
url="https://rxnav.nlm.nih.gov/REST/ndcstatus.json?ndc=00364666854"
out=resp;
run;

libname myfiles json 'location/demo.json';

But I am attempting to get a specific piece of data from this that I can't seem to parse out. When I use the following syntax:

data ndcstatus_testing;
set myfiles.ndcstatus;
run;

data ndcstatus_testing_all;
set myfiles.alldata;
run;

It seems as though I am only able to find these two pieces of data, but if I view the json file directly I can see what I'm trying to get at. I am trying to get the "ndcHistory" component of the file, which is also detailed at the following location:

https://rxnav.nlm.nih.gov/RxNormAPIs.html#uLink=RxNorm_REST_getNDCStatus

And below is what I can view in notepad when I open the json file directly.

Any idea why I can't seem to get to that piece of data, or how I could resolve it? It should include information like the date for certain codes.

Re: Extracting Data from JSON File from API

Reeza — Mon, 17 Jun 2019 18:52:38 GMT

When I run your code, there are 4 data tables present in my myFiles library. How many are present for you?

I can see dates in some of the data sets, specifically the NDCStatus_NDCHistory.

@A_SAS_Man wrote:

I am using the following code to access an API and retrieve some data:
filename resp 'location/demo.json';

proc http
method="GET"
url="https://rxnav.nlm.nih.gov/REST/ndcstatus.json?ndc=00364666854"
out=resp;
run;

libname myfiles json 'location/demo.json';
But I am attempting to get a specific piece of data from this that I can't seem to parse out. When I use the following syntax:
data ndcstatus_testing;
set myfiles.ndcstatus;
run;

data ndcstatus_testing_all;
set myfiles.alldata;
run;
It seems as though I am only able to find these two pieces of data, but if I view the json file directly I can see what I'm trying to get at. I am trying to get the "ndcHistory" component of the file, which is also detailed at the following location:

https://rxnav.nlm.nih.gov/RxNormAPIs.html#uLink=RxNorm_REST_getNDCStatus

And below is what I can view in notepad when I open the json file directly.

Any idea why I can't seem to get to that piece of data, or how I could resolve it? It should include information like the date for certain codes.

Re: Extracting Data from JSON File from API

Tom — Mon, 17 Jun 2019 18:52:35 GMT

It is there in one of the other datasets.

245 libname resp json ;
NOTE: JSON data is only read once. To read the JSON again, reassign the JSON LIBNAME.
NOTE: Libref RESP was successfully assigned as follows:
Engine: JSON
Physical Name: C:\Users\XXX\AppData\Local\Temp\1\SAS Temporary
Files\_TD528_AMRL20L6F1E4992_\#LN00057
246 proc copy inlib=resp outlib=work; run;

NOTE: Copying RESP.ALLDATA to WORK.ALLDATA (memtype=DATA).
NOTE: BUFSIZE is not cloned when copying across different engines. System Option for BUFSIZE was used.
NOTE: There were 21 observations read from the data set RESP.ALLDATA.
NOTE: The data set WORK.ALLDATA has 21 observations and 7 variables.
NOTE: Copying RESP.NDCSTATUS to WORK.NDCSTATUS (memtype=DATA).
NOTE: BUFSIZE is not cloned when copying across different engines. System Option for BUFSIZE was used.
NOTE: There were 1 observations read from the data set RESP.NDCSTATUS.
NOTE: The data set WORK.NDCSTATUS has 1 observations and 11 variables.
NOTE: Copying RESP.NDCSTATUS_NDCHISTORY to WORK.NDCSTATUS_NDCHISTORY (memtype=DATA).
NOTE: BUFSIZE is not cloned when copying across different engines. System Option for BUFSIZE was used.
NOTE: There were 1 observations read from the data set RESP.NDCSTATUS_NDCHISTORY.
NOTE: The data set WORK.NDCSTATUS_NDCHISTORY has 1 observations and 6 variables.
NOTE: Copying RESP.SOURCELIST_SOURCENAME to WORK.SOURCELIST_SOURCENAME (memtype=DATA).
NOTE: BUFSIZE is not cloned when copying across different engines. System Option for BUFSIZE was used.
NOTE: There were 1 observations read from the data set RESP.SOURCELIST_SOURCENAME.
NOTE: The data set WORK.SOURCELIST_SOURCENAME has 1 observations and 6 variables.
NOTE: PROCEDURE COPY used (Total process time):
real time 0.07 seconds
cpu time 0.01 seconds

251   data _null_;
252     set ndcstatus_ndchistory;
253     put (_all_) (=/);
254   run;


ordinal_ndcStatus=1
ordinal_ndcHistory=1
activeRxcui=
originalRxcui=312656
startDate=200706
endDate=201101

Re: Extracting Data from JSON File from API

A_SAS_Man — Mon, 17 Jun 2019 18:58:00 GMT

Thank you both, I could not figure out how to view the available files and their names in my setup.

Re: Extracting Data from JSON File from API

Tom — Mon, 17 Jun 2019 19:19:35 GMT

@A_SAS_Man wrote:
Thank you both, I could not figure out how to view the available files and their names in my setup.

PROC CONTENTS