topic Re: Macro for Retrieving API Data 2 in SAS Programming

Macro for Retrieving API Data 2

A_SAS_Man — Wed, 19 Jun 2019 14:57:28 GMT

I asked a very similar question yesterday here:

https://communities.sas.com/t5/SAS-Programming/Macro-to-Retrieve-API-Data/m-p/567064#M159422

I am having an almost identical issue with another macro, but can't seem to apply the solution that was provided to me yesterday.

data rxcui;
input rxcui $7.;
datalines;
1551300
1551306
1745108
2058877
358925
;
run;

%macro Query_RxClass_API(rxcui=);
filename resp2 'location/demo2.json';
options SSLCALISTLOC="location/trustedcerts.pem";

proc http
method="GET"
url="https://rxnav.nlm.nih.gov/REST/rxclass/class/byRxcui.json?rxcui=&rxcui&relaSource=ATC"
out=resp2;
run;

libname myfiles2 json 'location/demo2.json';

%_eg_conditional_dropds(WORK.atcdata);
data atcdata;
set myfiles2.rxclassdruginfo_rxclassminconc;
rxcui=&rxcui;
run;

%if %sysfunc(exist(myfiles2.RXCLASSDRUGINFO_MINCONCEPT))=1 %then %do;

data atcdata2;
set myfiles2.RXCLASSDRUGINFO_MINCONCEPT;
run;

proc sql;
create table allatcdata as
	select a.*,
		   b.rxcui as rxcui2,
		   b.name
	from atcdata a left join atcdata2 b on
		 (a.ordinal_rxclassDrugInfo=b.ordinal_rxclassDrugInfo)
;
quit;

proc append base=support.rxcui_to_atc data=allatcdata force;
run;


%end;

%else %do;
proc append base=support.rxcui_to_atc data=atcdata force;
run;
%end;

%mend Query_RxClass_API;

data macro_call_2;
set rxcuis;

str=catt('%Query_RxClass_API(rxcui=',rxcui, ');');

call execute(str);
run;

I tried to use the function described yesterday, but was unable to get that to work.

data macro_call_2;
set rxcuis;

str=catt('%nrstr(%Query_RxClass_API(rxcui=',rxcui, '));');

call execute(str);
run;

Yesterday the solver said I was running into "macro timing issues". If you are able to expand upon what is happening here so that I can hopefully understand future problems like this better I would appreciate it.

Re: Macro for Retrieving API Data 2

Reeza — Wed, 19 Jun 2019 15:23:12 GMT

For debugging this, first can you call the macro manually and make sure it works.

This would be submitting:

%query_rxClass_api(rxcui=someVAlue);

And then check the macro_call_2 data set and ensure that the STR variable is being created correctly. I don't think you need the %nrstr() for this one as well so you can delete that and the extra brackets.

str=catt('%Query_RxClass_API(rxcui=',rxcui, ');');

Re: Macro for Retrieving API Data 2

A_SAS_Man — Wed, 19 Jun 2019 17:51:53 GMT

Thank you for the tip. I was able to figure out what I was doing wrong, I think I did still need that function for it to work. Working macro below for any who are interested.

Edit: A little elaboration on the issue. I was assuming that one of the two datasets I was pulling from the API would always exist, which was false and was causing my logic to break. I then needed to use the same function from my linked example to call the macro successfully.

%macro Query_RxClass_API(rxcui=);
filename resp2 'location/demo2.json';
options SSLCALISTLOC="location/trustedcerts.pem";

proc http
method="GET"
url="https://rxnav.nlm.nih.gov/REST/rxclass/class/byRxcui.json?rxcui=&rxcui&relaSource=ATC"
out=resp2;
run;

libname myfiles2 json '/location/demo2.json';

%if %sysfunc(exist(myfiles2.rxclassdruginfo_rxclassminconc))=1 and %sysfunc(exist(myfiles2.RXCLASSDRUGINFO_MINCONCEPT))=1 
%then %do;

%_eg_conditional_dropds(WORK.atcdata);
data atcdata;
set myfiles2.rxclassdruginfo_rxclassminconc;
rxcui=&rxcui;
run;

data atcdata2;
set myfiles2.RXCLASSDRUGINFO_MINCONCEPT;
run;

proc sql;
create table allatcdata as
	select a.*,
		   b.rxcui as rxcui2,
		   b.name
	from atcdata a left join atcdata2 b on
		 (a.ordinal_rxclassDrugInfo=b.ordinal_rxclassDrugInfo)
;
quit;

proc append base=support.rxcui_to_atc data=allatcdata force;
run;
%end;

%if %sysfunc(exist(myfiles2.rxclassdruginfo_rxclassminconc))=1 and %sysfunc(exist(myfiles2.RXCLASSDRUGINFO_MINCONCEPT))=0 
%then %do;

%_eg_conditional_dropds(WORK.atcdata);
data atcdata;
set myfiles2.rxclassdruginfo_rxclassminconc;
rxcui=&rxcui;
run;

proc append base=support.rxcui_to_atc data=atcdata force;
run;
%end;

%else %do;

%end;
%mend Query_RxClass_API;

data macro_call_2;
set rxcuis;

str=catt('%nrstr(%Query_RxClass_API(rxcui=',rxcui, '));');

call execute(str);
run;