Macro quoting for a url

[JacquesR]

I have a large number of API queries to work through. I have a dataset with a start and end date, and an ID. I will use CALL EXECUTE to work through each row of that dataset, running a macro to get the data using PROC HTTP.

My main problem is actually getting the macro to deal with the parameters (prefixed with ampersand) and macro variables properly.

The url should look like this when finished:

https://api....?auth=xyz&id=1234567890&start=2009-01-01&end=2009-01-02

But that is not what I am getting.

When I do the steps individually, they give me what I want (see my various %put statements below), but when included in the %SYSFUNC, they don't work out.

When I run the %put statements, I get what I need.

When I run the %LET URL=%SYSFUNC(CATX statement, I get:

WARNING: Apparent symbolic reference ID not resolved.
WARNING: Apparent symbolic reference START not resolved.
WARNING: Apparent symbolic reference END not resolved.

And the Start and End dates are evaluated as if they were calculations.

And the url is being left truncated.

url is:

xyz&id=1234567890&start=2017&end=2016

What am I missing?

%let auth=xyz;
%let monitor=1234567890;
%let startdate=2019-01-01;    
%let enddate=2019-01-02;
%put %nrstr(&id=);
%put %str(&monitor);
%put %nrstr(&start=);
%put %str(&startdate);
%put %nrstr(&end=);
%put %str(&enddate);
%LET URL=%SYSFUNC(CATX(,https://api....?auth=,&Auth,%nrstr(&id=),&Monitor,%nrstr(&start=),%str(&StartDate),%nrstr(&end=),%str(&EndDate)));
%put &url;

Thanks in advance

[gamotte]

Hello,

Use a data step instead.

data have;
auth="xyz";
monitor="1234567890";
startdate='01Jan2019'd;
enddate='02Jan2019'd;
run;

data _NULL_;
set have;
length url $200;
url=cats('https://api....?auth=', auth,'&id=', monitor,'&start=',startDate,'&end=',EndDate);
call execute(...);
run;

[JacquesR]

Hi @gamotte 

I owe you an apology. As I have indicated in a new thread I started on how macros compile (https://communities.sas.com/t5/SAS-Programming/Understanding-macro-compiling/m-p/644117), I see that while the solutions provided did resolve my issue of quoting, they did not let my macro work properly, because the url was not being 'reconstructed' each time CALL EXECUTE initiated the macro. So it really was necessary to construct the url in the data step.

This did also allow me to simplify my macro considerably, which was a bonus.

What I ended up with was this (a combination of the quoting solutions suggested by @Quentin and @Tom , and your data step suggestion):

DATA _NULL_;
    SET MonitorsAndDates;
    URL=CATS("'https://api.....com/api/monitor?auth=",SYMGET('Auth'),%NRSTR('&id='),Monitor,%NRSTR('&start='),PUT(StartDate,E8601DA10.),%NRSTR('&end='),PUT(EndDate,E8601DA10.),"'");
    CALL EXECUTE('%GetVolume(MonitorLabel='||MonitorLabel||',N='||_N_||',URL='||URL||');');
RUN;

&Auth is a global macro variable created through an earlier PROC HTTP authentication request which uses the username and password, while Monitor, MonitorLabel, StartDate, EndDate are all variables in WORK.MonitorsAndDates.

Thanks for that suggestion.

[Quentin · Accepted solution]

You shouldn't need CATX for macro language concatenation.  How about just:

%LET URL=https://api....?auth=&Auth%nrstr(&id=)&Monitor%nrstr(&start=)&StartDate%nrstr(&end=)&EndDate;
%put &url;

[JacquesR]

Thanks for all the responses.

@gamotte, it was not quite that simple, since I also included macro variables from further upstream, and thus building the URL, while technically possible in a data step, was not quite my ideal.

@Quentin, you were right, I was obviously overthinking the whole thing, and removing the CATX and SYSFUNC did the trick (mostly).

I did come across something which I thought I should share.

So as referred to above, I modified some of my earlier code on the basis of this as well.

Before being able to submit the API queries, I first have to get an authentication key for the session. So I had this in open code (not in a macro):

%LET URL=%SYSFUNC(CATX(,https://api.....com/api/authenticate?username=,&UserName,&password=,&pwd));

It was also giving the warning about not being able to resolve some of the presumed macro variables, but was giving me a url which I could successfully use in PROC HTTP to get my authentication key and store in the macro variable &Auth.

But I did change that to:

%LET url=https://api.....com/api/authenticate?username=&UserName%NRSTR(&password=)&pwd;
PROC HTTP url="&url" method="GET" out=token;run;

The important thing for this follow-up is the PROC HTTP url="&url" part.

My macro started like this:

%MACRO GetVolume(StartDate=,EndDate=,Monitor=,MonitorLabel=);

%LET url=https://api.....com/api/monitor?auth=&Auth%NRSTR(&id=)&Monitor%NRSTR(&start=)&StartDate%NRSTR(&end=)&EndDate;
/*%put &url;*/
FILENAME Vol TEMP;
PROC HTTP 
url=%NRSTR("&url")
method="GET"
out=Vol;
run;
LIBNAME Volumes JSON FILEREF=Vol;

Note that in the macro, as opposed to in the open code, I still had to mask &url in the PROC HTTP step.

As for getting the dates in the right format, that was done during the call:

DATA _NULL_;
    SET test;
    CALL EXECUTE('%GetVolume(StartDate='||PUT(StartDate,E8601DA10.)||',EndDate='||PUT(EndDate,E8601DA10.)||',Monitor='||Monitor||',MonitorLabel='||MonitorLabel||');');
RUN;

@Quentin and @Tom, thanks also for the further explanations. That helped me understand what was going on, and I think I have learned a lot through this. My code is happily running away now (close to two thousand API calls), so I am quite happy with the result.

Thanks

[Tom]

Since you need to end up with the URL inside quotes, by putting it inside of single quotes then you can avoid later attempts to treat the &'s as macro triggers.

For example:

%LET url=%sysfunc(quote(https://api.....com/api/authenticate?username=&UserName%NRSTR(&password=)&pwd,%str(%')));
PROC HTTP url=&url method="GET" out=token;run;

[JacquesR]

Thanks @Tom , that is sensible, and works for both the open code and the macro, so I have taken that on board.

[Quentin]

I'm sticking with my answer to avoid using CATX(), but I was curious as to root cause, so dug a bit more.

It looks like when you code (I changed to QSYSFUNC):

%let URL=%QSYSFUNC(CATX(,https://api....?auth=,&Auth,%nrstr(&id=),&Monitor,%nrstr(&start=),%str(&StartDate),%nrstr(&end=),%str(&EndDate)));

the CATX function is unquoting the arguments, and it sees the = sign in the second argument and decides that is is a boolean expression which should be evaluated to 0 or 1.  The log is:

1    %let auth=xyz;
2    %let monitor=1234567890;
3    %let startdate=2019-01-01;
4    %let enddate=2019-01-02;
5    %let
5  ! URL=%QSYSFUNC(CATX(,https://api....?auth=,&Auth,%nrstr(&id=),&Monitor,%nrstr(&start=),%str(&S
5  ! tartDate),%nrstr(&end=),%str(&EndDate)));
WARNING: Apparent symbolic reference ID not resolved.
WARNING: Apparent symbolic reference START not resolved.
WARNING: Apparent symbolic reference END not resolved.
6    %put &url;
0xyz&id=1234567890&start=2017&end=2016

Note the 0 at the beginning, that is because CATX evaluated the expression h ttps://api....?auth= as false, and returned a 0. Note also that SAS tried to resolve references to macro variables ID START and END, even though you appropriately quoted them using %NRSTR.  

If you remove the = sign from the second argument, it works:

7    %LET
7  ! URL=%QSYSFUNC(CATX(,https://api....?auth,&Auth,%nrstr(&id=),&Monitor,%nrstr(&start=),%str(&St
7  ! artDate),%nrstr(&end=),%str(&EndDate)));
WARNING: Apparent symbolic reference ID not resolved.
WARNING: Apparent symbolic reference START not resolved.
WARNING: Apparent symbolic reference END not resolved.
8    %put &url;
https://api....?authxyz&id=1234567890&start=2017&end=2016

I don't see an easy way to tell CATX that the second argument with an equals sign is a text string rather than an expression.  I thought adding %STR() might help (as it did for the %nrstr(id=) ), but it didn't:

11   %LET
11 ! URL=%QSYSFUNC(CATX(,https://api....?auth%nrstr(=),&Auth,%nrstr(&id=),&Monitor,%nrstr(&start=)
11 ! ,%str(&StartDate),%nrstr(&end=),%str(&EndDate)));
WARNING: Apparent symbolic reference ID not resolved.
WARNING: Apparent symbolic reference START not resolved.
WARNING: Apparent symbolic reference END not resolved.
12   %put &url;
0xyz&id=1234567890&start=2017&end=2016

One option would be to URLENCODE the problematic string, which will convert the = sign to %3D and thereby avoid the problem, and it's probably a good best practice to URLENCODE anyway:

16   %LET
16 ! URL=%QSYSFUNC(CATX(,%qsysfunc(urlencode(https://api....?auth=)),&Auth,%nrstr(&id=),&Monitor,%
16 ! nrstr(&start=),%str(&StartDate),%nrstr(&end=),%str(&EndDate)));
WARNING: Apparent symbolic reference ID not resolved.
WARNING: Apparent symbolic reference START not resolved.
WARNING: Apparent symbolic reference END not resolved.
17   %put &url;
https%3A%2F%2Fapi....%3Fauth%3Dxyz&id=1234567890&start=2017&end=2016

I think the central problem is that CATX has more functionality than most functions (accepting character values, numeric values, and expressions), and when called via %SYSFUNC there doesn't seem to a be a good way to tell it that a value is a string.  

[Quentin]

Noticed I didn't solve part of your question, which was to avoid 2019-01-01 evaluating to 2017.  URLENCODE doesn't prevent that.  It triggered a memory of this old thread, where I proposed an ugly hack of adding quote marks to indicate to CATX that values are strings, and then removing them, e.g.:

%let auth=xyz;
%let monitor=1234567890;
%let startdate=2019-01-01;    
%let enddate=2019-01-02;
%let URL=%Qsysfunc(compress(%SYSFUNC(CATX(|,"https://api....?auth=",&Auth,'&id=',&Monitor,'&start=',"&StartDate",'&end=',"&EndDate")),|%str(%')%str(%")));
%put &url;

Which returns:

5    %let
5  ! URL=%Qsysfunc(compress(%SYSFUNC(CATX(|,"https://api....?auth=",&Auth,'&id=',&Monitor,'&start=
5  ! ',"&StartDate",'&end=',"&EndDate")),|%str(%')%str(%")));
6    %put &url;
https://api....?auth=xyz&id=1234567890&start=2019-01-01&end=2019-01-02

[Tom]

The reason it is a problem is because there is no data step compiler when using %sysfunc() macro function to call SAS functions that can pass to the SAS function what types of arguments it is getting.

If you really,really want a macro version of CATX() it is not that hard to write.

%macro qcatx(dlm) /parmbuff;
%local i n word;
%if %length(&syspbuff)>2 %then %do;
  %let syspbuff = %qsubstr(&syspbuff,2,%length(&syspbuff)-2);
  %let n=0;
  %do i=2 %to %sysfunc(countw(&syspbuff,%str(,),qm));
    %let word=%qscan(&syspbuff,&i,%str(,),qm);
    %if %length(&word) %then %do;
      %if (&n) %then %do;&dlm.%end;&word.
      %let n=1;
    %end;
  %end; 
%end;
%mend qcatx;

%macro catx() /parmbuff;
%unquote(%qcatx&syspbuff)
%mend catx;

Testing

520   %put |%catx(/,b,,c,,d,f,,)|;
|b/c/d/f|
521   %put |%catx()|;
||
522   %put |%catx|;
||
523   %put |%catx(x)|;
||
524   %put |%catx(x,y)|;
|y|
525   %let x=value of x;
526   %put |%qcatx(/,a,%nrstr(&x),b)|;
|a/&x/b|
527   %put |%catx(/,a,%nrstr(&x),b)|;
|a/value of x/b|
528   %put |%catx(/,'a,c','b')|;
|'a,c'/'b'|
529   %put |%catx(,a,b,c,,d)|;
|abcd|

 If you want it to ignore the spaces around the the items in a call like this:

%put |%catx(,a, b ,c,,d   )|;

Then you can make a change for the un-macro-quoted version (instead of just unquoting the quoted version).

%macro catx(dlm) /parmbuff;
%local i n word;
%if %length(&syspbuff)>2 %then %do;
  %let syspbuff=%substr(&syspbuff,2,%length(&syspbuff)-2);
  %let syspbuff=%superq(syspbuff);
  %let n=0;
  %do i=2 %to %sysfunc(countw(&syspbuff,%str(,),qm));
    %let word=%scan(&syspbuff,&i,%str(,),qm);
    %if %length(&word) %then %do;
      %if (&n) %then %do;&dlm.%end;&word.
      %let n=1;
    %end;
  %end; 
%end;
%mend catx;

[Quentin]

Thanks @Tom , 

That's a helpful explanation:

---

@Tom wrote:

The reason it is a problem is because there is no data step compiler when using %sysfunc() macro function to call SAS functions that can pass to the SAS function what types of arguments it is getting.

For those functions that accept both character and numeric arguments, I thought the function would be responsible for deciding which type it received.  But makes more sense that the data step compiler tells the function what it's getting.  So seems more understandable why there isn't a good way to tell  CAT  what type of arguments it's getting when it's called by %SYSFUNC.

In general, I don't love the idea of functions that will accept both character and numeric arguments.