topic Re: Passing comma-separated list to call execute in SAS Programming

Passing comma-separated list to call execute

JacquesR — Tue, 20 Jul 2021 07:45:59 GMT

I feel like to a total dunce.

I just cannot seem to grasp string encoding for macros, despite my previous questions:

Re: Understanding macro compiling - SAS Support Communities

Solved: Re: Macro quoting for a url - SAS Support Communities

Solved: Re: CALL SYMPUT within a macro generated using CAL... - SAS Support Communities

In short, I am using (or hoping to use) the Twitter API to retrieve tweets.

Tweet lookup - GET /2/tweets | Docs | Twitter Developer Platform

The API allows me to specify up to 100 Tweet IDs in a comma-separated list with no spaces.

This is a sample API call from the abovementioned page:

"https://api.twitter.com/2/tweets?ids=1261326399320715264,1278347468690915330"

I have created a dataset with the variable TweetList that is a list of up to 100 of these IDs, separated by commas.

So far, so good.

Now for each row in this dataset, I want to send out the API call, get and parse the result, and then append it to a dataset.

I won't bore you with the details of the whole macro--the code within works when I run it with a single Tweet ID, and I adapted code from here to create it: https://blogs.sas.com/content/sascom/2013/12/12/how-to-import-twitter-tweets-in-sas-data-step-using-oauth-2-authentication-style/

The macro header is:

%MACRO GetTweets(TweetList=,N=);
   %let JSON_TWEET_FILE=&OutputPath.ResponseContent&N..txt;
   %let CSV_TWEET_FILE=&OutputPath.ResponseContent&N..csv;
   FILENAME response &JSON_TWEET_FILE.;
...

N is just so that I can store the results in a sequence of data files, one for each run.

If it's any help, the total length (using LENGTHN) for TweetList varies between 1903 and 1923 characters.

If you want to test my code below, this simplified macro should work:

%MACRO GetTweets(TweetList=,N=);
%PUT &TweetList;
%PUT &N;
%MEND;

I actually have two problems. I will post two variations of the code, and the errors raised, to highlight the issues. In the code below I replace real Tweet IDs with the IDs from Twitter's example.

First, despite my defining a length of $4000. for the statement, it is not accepting the whole list:

DATA _NULL_;
LENGTH _call $4000.;
SET TWITTER.NootropicTweetListsForAPI;
	 _call='%nrstr('||'%GetTweets(TweetList='||%NRSTR(TweetList)||',N='||_N_||'));';
	PUT _call=;
	CALL EXECUTE(_call);
RUN;

NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
      573:70
_call=%nrstr(%GetTweets(TweetList=1261326399320715264,1278347468690915330,<Lots more IDs>,1261326399320715264,1278347468690915330
ERROR: Expected close parenthesis after macro function invocation not found.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: There were 625 observations read from the data set TWITTER.NOOTROPICTWEETLISTSFORAPI.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

NOTE: CALL EXECUTE generated line.
ERROR: All positional parameters must precede keyword parameters.
1   + %GetTweets
NOTE: Line generated by the CALL EXECUTE routine.
2   +(TweetList=1261326399320715264,1278347468690915330,1261326399320715264,1278347468690915330,....
                                                        ------------------
                                                        180
2  !+1261326399320715264,1278347468690915330
ERROR 180-322: Statement is not valid or it is used out of proper order.

If I reduce the length passed through, the whole string is created, but the problem surrounding the comma separating the Tweet IDs remains:

DATA _NULL_;
LENGTH _call $4000.;
SET TWITTER.NootropicTweetListsForAPI;
	_call='%nrstr('||'%GetTweets(TweetList='||%NRSTR(SUBSTRN(TweetList,1,58))||',N='||_N_||'));';
	PUT _call=;
	CALL EXECUTE(_call);
RUN;

NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
      582:83
_call=%nrstr(%GetTweets(TweetList=1261326399320715264,1278347468690915330,1261326399320715264,N=           1));
NOTE: There were 625 observations read from the data set TWITTER.NOOTROPICTWEETLISTSFORAPI.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

NOTE: CALL EXECUTE generated line.
ERROR: All positional parameters must precede keyword parameters.
NOTE: Line generated by the CALL EXECUTE routine.
1   + %GetTweets(TweetList=1261326399320715264,1278347468690915330,1261326399320715264,N=           1);
                                                                   ------------------
                                                                   180

ERROR 180-322: Statement is not valid or it is used out of proper order.

Any help would be appreciated. And perhaps an explanation of what I am missing that is keeping me from figuring this out, even though this is evidently not the first time I have grappled with this topic.

Thanks

Re: Passing comma-separated list to call execute

Kurt_Bremser — Tue, 20 Jul 2021 08:01:29 GMT

Make your life easier by using another delimiter in a string you want to use as a macro parameter.

If you use "|" as delimiter, you can use

data _null_;
length tweetlist $32767;
tweetlist = translate("&tweetlist.",",","|");
call symputx("tweetlist",tweetlist,"l");
run;

in the macro to create the comma-separated list where needed.

Re: Passing comma-separated list to call execute

ChrisNZ — Tue, 20 Jul 2021 09:08:55 GMT

You could try

 _CALL='%nrstr('||'%GetTweets(TweetList=%str('||TWEETLIST||'),N='||_N_||'));';

or even

 _CALL=catt( '%GetTweets(TweetList=%str(', TWEETLIST, '), N=',  _N_, ');' );

Re: Passing comma-separated list to call execute

JacquesR — Tue, 20 Jul 2021 09:30:44 GMT

So something like this?

%MACRO GetTweets2(TweetList=,N=);
%PUT &TweetList;
%PUT &N;
%LET TweetList2=%SYSFUNC(TRANSTRN("&TweetList","|",","));
DATA _NULL_;
LENGTH tmp $4000.;
tmp=TRANSTRN("&TweetList","|",",");
call symputx("TweetList2",tmp,"l");
run;
%PUT &TweetList2;
%MEND;
DATA _NULL_;
LENGTH _call $4000.;
SET TWITTER.NootropicTweetListsForAPI;
	TweetList=TRANSTRN(TweetList,",","|");
 _call='%nrstr('||'%GetTweets2(TweetList='||%NRSTR(TRIMN(TweetList))||',N='||_N_||'));';
	PUT _call=;
	CALL EXECUTE(_call);
RUN;

Why does the sysfunc not work?

I get a couple of these, but the put statements seem to produce the right result:

NOTE: The quoted string currently being processed has become more than 262 characters long.  You might have unbalanced quotation marks.

Re: Passing comma-separated list to call execute

Kurt_Bremser — Tue, 20 Jul 2021 10:42:42 GMT

Make sure that the variable tweetlist has pipe characters instead of commas, then simplify the_call variable:

_call='%nrstr(%GetTweets2(TweetList='||trim(TweetList)||',N='||strip(put(_N_,best.))||'));';

Re: Passing comma-separated list to call execute

JacquesR — Tue, 20 Jul 2021 23:32:13 GMT

Hi Chris Ah, I tried %STR outside the quotes, not inside. Good insight.

However, both your statements still produce this error:

NOTE: CALL EXECUTE generated line.
NOTE: Line generated by the CALL EXECUTE routine.
1   + %nrstr(%GetTweets2(TweetList=%str
      -
      180
ERROR 180-322: Statement is not valid or it is used out of proper order.

Re: Passing comma-separated list to call execute

ChrisNZ — Tue, 20 Jul 2021 23:42:11 GMT

That's a different error, linked to something else you do. The comma-delimited list works.

%macro GetTweets(TweetList=,N=); %put ====> &=Tweetlist &=N; %mend;
 data _null_;
   TWEETLIST='a,b,c';
  _CALL='%nrstr('||'%GetTweets(TweetList=%str('||TWEETLIST||'),N='||_N_||'));';
   call execute(_CALL);
  _CALL=catt( '%GetTweets(TweetList=%str(', TWEETLIST, '), N=',  _N_, ');' );
  call execute(_CALL);
run;

32          data _null_;
33            TWEETLIST='a,b,c';
34           _CALL='%nrstr('||'%GetTweets(TweetList=%str('||TWEETLIST||'),N='||_N_||'));';
35            call execute(_CALL);
36           _CALL=catt( '%GetTweets(TweetList=%str(', TWEETLIST, '), N=',  _N_, ');' );
37           call execute(_CALL);
38         run;

NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
      34:69   
====> TWEETLIST=a,b,c N=1
NOTE: DATA statement used (Total process time):
      real time           0.11 seconds
      user cpu time       0.01 seconds
      system cpu time     0.01 seconds
      memory              241.34k
      OS Memory           17748.00k
      Timestamp           21/07/2021 11:41:34 AM
      Step Count                        35  Switch Count  48
      

NOTE: CALL EXECUTE generated line.
1         + %GetTweets(TweetList=%str(a,b,c),N=           1);
====> TWEETLIST=a,b,c N=1

Re: Passing comma-separated list to call execute

Tom — Wed, 21 Jul 2021 00:31:28 GMT

So your error is because you are generating this macro call:

%GetTweets(TweetList=1261326399320715264,1278347468690915330,....

Which is clearly invalid. You are passing the first parameter value, 1261326399320715264, by name and the following that with trying to pass another parameter value by position.

You don't show either how you are generating the dataset variable, TweetList, or what the macro %GETTWEETS() is expects in the input parameter TWEETLIST. But it will work much easier if you replace the commas in your list of tweets with some other character. Since the values look to be digit strings just use spaces.

There is normally no advantage to adding the %NRSTR() around the parameter values, and it can make somethings hard, like referencing existing macro variables. Just wrap the %macroname part in the %NRSTR() macro function so that the macro does not run while CALL EXECUTE() is running and instead runs when the code is pulled back off the stack after the data step ends.

data _null_;
  set twitter.nootropictweetlistsforapi;
  call execute(cats('%nrstr(%gettweets)(tweetlist=',translate(tweetlist,' ',','),',n=',_n_,')'));
run;