space separated list intersection

1
votes

Note: code edited after remarks from @user667489 but issues remain.

I built a fairly simple macro to return the intersection of 2 space separated lists as a new space separated list but for some reason the definition of the macro returns errors.

The macro loops through both lists and keep an element if a match is found (very straightforward, no handling of duplicates or optimization).

I cannot make sense of the log, which shows a combination of following error messages:

ERROR: Macro keyword LET appears as text.

ERROR: Macro keyword MACRO appears as text.

%macro list_intersection
(list1= /* space separated list, or unique term */
,list2= /* space separated list, or unique term */
);
%local output;
%local i;
%local j;
%let i = 1;
%let j = 1;
%do %while (%length(%scan(&list1,&i)));
  %do %while (%length(%scan(&list2,&j)));
    %if (%scan(&list1,&i) = %scan(&list2,&j)) %then
      %let output = &output %scan(&list1,&i);
    %let j = %eval(&j+1);
  %end;
  %let i = %eval(&i+1);
%end;
&output
%mend;

Can you help me circling the issue ?

I'm also open to a more efficient/robust/simple way of achieving the same output.

Reproducible log

Using SAS 9.3, I put above code in a separate program for it not to be polluted, save project, close and reopen. Open program, click run button, and here is the complete log:

1                                                          The SAS System                             09:50 Monday, January 22, 2018

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='Program3';
ERROR: Macro keyword LET appears as text.
4          %LET _CLIENTPROJECTPATH='F:\CI\Projects\Wealth Indicators\106 Explore DIM_PHYSICALPERSON\SAS\Rework_table_auto2.egp';
ERROR: Macro keyword LET appears as text.
5          %LET _CLIENTPROJECTNAME='Rework_table_auto2.egp';
ERROR: Macro keyword LET appears as text.
6          %LET _SASPROGRAMFILE=;
ERROR: Macro keyword LET appears as text.
7          
8          ODS _ALL_ CLOSE;
9          OPTIONS DEV=ACTIVEX;
10         GOPTIONS XPIXELS=0 YPIXELS=0;
11         FILENAME EGSR TEMP;
12         ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR STYLE=HtmlBlue
12       ! STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/x86/SASEnterpriseGuide/5.1/Styles/HtmlBlue.css") NOGTITLE NOGFOOTNOTE
12       !  GPATH=&sasworklocation ENCODING=UTF8 options(rolap="on");
13         
14         GOPTIONS ACCESSIBLE;
15             %macro list_intersection
ERROR: Macro keyword MACRO appears as text.
16             (list1= /* space separated list, or unique term */
17             ,list2= /* space separated list, or unique term */
18             );
19             %local output;
ERROR: Macro keyword LOCAL appears as text.
20             %local i;
ERROR: Macro keyword LOCAL appears as text.
21             %local j;
ERROR: Macro keyword LOCAL appears as text.
22             %let i = 1;
ERROR: Macro keyword LET appears as text.
23             %let j = 1;
ERROR: Macro keyword LET appears as text.
24             %do %while (%length(%scan(&list1,&i)));
ERROR: Macro keyword DO appears as text.
25               %do %while (%length(%scan(&list2,&j)));
ERROR: Macro keyword DO appears as text.
26                 %if (%scan(&list1,&i) = %scan(&list2,&j)) %then
ERROR: Macro keyword IF appears as text.
27                   %let output = &output %scan(&list1,&i);
28              %let j = %eval(&j+1);
ERROR: Macro keyword LET appears as text.
29               %end;
ERROR: Macro keyword END appears as text.
30               %let i = %eval(&i+1);
ERROR: Macro keyword LET appears as text.
31             %end;
ERROR: Macro keyword END appears as text.
32             &output
33             %mend;
ERROR: Macro keyword MEND appears as text.
34         
35         GOPTIONS NOACCESSIBLE;
36         %LET _CLIENTTASKLABEL=;
ERROR: Macro keyword LET appears as text.
37         %LET _CLIENTPROJECTPATH=;
2                                                          The SAS System                             09:50 Monday, January 22, 2018

ERROR: Macro keyword LET appears as text.
38         %LET _CLIENTPROJECTNAME=;
ERROR: Macro keyword LET appears as text.
39         %LET _SASPROGRAMFILE=;
ERROR: Macro keyword LET appears as text.
40         
41         ;*';*";*/;quit;run;
42         ODS _ALL_ CLOSE;
43         
44         
45         QUIT; RUN;
46

Initial code before edit:

%macro list_intersection
    (list1= /* space separated list, or unique term */
    ,list2= /* space separated list, or unique term */
    );
    %local output =;
    %local i = 1;
    %local j = 1;
    %do %while (%length(%scan(&list1,&i)));
      %do %while (%length(%scan(&list2,&j)));
        %if (%scan(&list1,&i) = %scan(&list2,&j) %then
          %local output = &output %scan(&list1,&i);
        %let j = %eval(&j+1);
      %end;
      %let i = %eval(&i+1);
    %end;
    &output
    %mend;
2
sassas-macro
Please include an example of how you are running your macro and the complete resulting log output. - user667489
I added the log - Moody_Mudskipper
When I had to write complex SAS code I wrote a %generate_table_from_list() macro, then the macro you're trying to write could be implemented with two calls to create tables from the input lists, followed by a PROC SQL to intersect them and SELECT the result INTO the output variable. This implementation cannot be used in context where no intermediate STEPs or PROCs can be generated, but it almost never was a problem for me. - Nickolay
As for ERROR: Macro keyword LET appears as text., I'd restart EG (or reconnect to the app server). - Nickolay
Thank you Nickolay, indeed I had to restart EG, I wrongly supposed reopening the project without closing EG would be enough. I thought about your approach as I'm on my way to build a %unique macro to eliminate duplicates and I master SQL better than SAS, but I thought creating temp tables and going through SQL may be bad practice, isn't it less efficient ? - Moody_Mudskipper

2 Answers

2
votes

A few things immediately stand out:

  1. You cannot use %local to set a value for a macro variable. Instead of %local i=1; you must write two separate statements: %local i; %let i = 1;. %local initialises macro variables to an empty string.
  2. You have unbalanced brackets in your %if statement.
  3. Try moving %let j = %eval(&j+1); into the outer %do %while loop.
  4. Also, you probably want to make sure that %scan only uses space as a delimiter - it defaults to space plus . < ( + & ! $ * ) ; ^ - / , % |

Here's a working version:

%macro list_intersection
(list1= /* space separated list, or unique term */
,list2= /* space separated list, or unique term */
);
%local output;
%local i;
%local j;
%let i = 1;
%do %while (%length(%scan(&list1,&i,%str( ))));
  %let j = 1;
  %do %while (%length(%scan(&list2,&j,%str( ))));
    %if %scan(&list1,&i,%str( )) = %scan(&list2,&j,%str( )) %then
      %let output = &output %scan(&list1,&i,%str( ));
    %let j = %eval(&j+1);
  %end;
  %let i = %eval(&i+1);
%end;
&output
%mend;

%put %list_intersection(list1=1 2 3,list2=2 3 4);
1
votes

You can use SAS functions to make that much easier. Use the COUNTW() function to find the upper bound for the %DO loop. Use the FINDW() function to test if word is found in the other list.

%macro list_intersection
(list1 /* space separated list of terms */
,list2 /* space separated list of terms */
);
%local output i next ;
%do i=1 %to %sysfunc(countw(&list1,%str( ))) ;
  %let next=%scan(&list1,&i,%str( ));
  %if %sysfunc(findw(&list2,&next,,s)) %then %let output=&output &next ;
%end;
&output
%mend;

You could include the i modifier in the findw() call to make it case insensitive. You could also test if the word is already in the output string to eliminate duplicates.

%macro list_intersection_nodups
(list1 /* space separated list of terms */
,list2 /* space separated list of terms */
);
%local output i next ;
%do i=1 %to %sysfunc(countw(&list1,%str( ))) ;
  %let next=%scan(&list1,&i,%str( ));
  %if %sysfunc(findw(&list2,&next,,si)) and not %sysfunc(findw(&output,&next,,si))
  %then %let output=&output &next ;
%end;
&output
%mend;

Example:

274  %put %list_intersection_nodups(A B a C,a c d);
A C