Macro to Retrieve API Data

A_SAS_Man — Tue, 18 Jun 2019 20:40:17 GMT

I am trying to write a macro that will potentially pull 2 datasets from an api, join them and append to an existing dataset. I am trying to use some conditional logic because the second dataset is not available for all values, so if the second one doesn't exist, just append the first without attempting a join.

data ndc;
input ndc $11.;
datalines;
00002140701
00002143361
00000000000
00000000005
;
run;

data ndc;
input ndc $11.;
datalines;
00002140701
00002143361
00000000000
00000000005
;
run;

%macro Query_RxNav_API(ndc=);
filename resp 'location/demo.json';
options SSLCALISTLOC="locations/trustedcerts.pem";

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

libname myfiles json 'location/demo.json';

data ndcstatus;
set myfiles.ndcstatus;
run;

%if %sysfunc(exist(myfiles.ndcstatus_ndchistory))=1 %then %do;

data ndchistory;
set myfiles.ndcstatus_ndchistory;
run;

proc sql;
create table ndc_join as
	select a.*,
		   b.*
	from ndcstatus a left join ndchistory b on
		 (a.ordinal_root=b.ordinal_ndcStatus)
;
quit;

proc append base=support.ndc_to_rxcui data=ndc_join force;
run;


%end;

%else %do;
proc append base=support.ndc_to_rxcui data=ndcstatus force;
run;

%end;
%mend Query_RxNav_API;

data macro_call;
set ndc;

str=catt('%Query_RxNav_API(ndc=',ndc, ');');

call execute(str);
run;

When I run this macro I get the following error:

ERROR: File MYFILES.NDCSTATUS_NDCHISTORY.DATA does not exist.

My logic seems to be failing at the if statement, because if that file doesn't exist it shouldn't need to use it, right? I have also tried the below syntax and received a similar error.

%if %sysfunc(exist(myfiles.ndcstatus_ndchistory)) %then %do;

Any idea what is causing my logic to to skip the check of the file that doesn't exist?

Re: Macro to Retrieve API Data

Kurt_Bremser — Tue, 18 Jun 2019 20:54:20 GMT

You're falling victim to macro timing issues when using call execute. Expand your command for call execute:

str=catt('%nrstr(%Query_RxNav_API(ndc=',ndc, '));');

topic Macro to Retrieve API Data in SAS Programming

Macro to Retrieve API Data

Re: Macro to Retrieve API Data