SAS macros multiple statements

Question

I start with some example. I have "schools" variable like "harvard and oxford", "only harvard" etc. I want to find in "schools" variable only these cases which contain only one school name ("place" variable). I have "place" variable in dataset bbb which I put in listplace ("harvard!oxford!wse), "opinion" variable which I put in listopinion ("8!8!9) and "country" variable which I put in listcountry ("usa!uk!france"). I want to make macro which find in "schools" variable only these cases which contains two school names (zm1). Then, I want to add to them appropriate item from listopinion and listplace. "Place" is school name, "opinion" is average rate and "country" is origin of this rate.

That's my first dataset: place opinion country - "harvard" 8 "usa" - "oxford" 8 "uk" - "wse" 9 "france"

That's my second dataset:

schools - harvard best - only harvard should be chosen - we dont't have any values - cake

That's what I want to have as results of my code (only two observation, not four as it is in "schools" variable):

schools opinion country - "harvard very special" 8 "usa" - "only harvard should be chosen" 8 "usa"

How can I make three %do until loops with %let statements?

    data aaa;
    input  schools;
    datalines;
    "harvard and oxford"
    "only harvard should be chosen"
    "cheese, cake, pizza"
    "we dont't have any values"
    ;
    run;
    data bbb;
    input  place opinion country;
    datalines;
    "harvard" 8 "usa"
    "oxford" 8 "uk"
    "wse" 9 "france"
    ;
    run;
    data ccc;
    set
    proc sql noprint;
    select opinion
    into :listopinion separated by "!"
    from aaa
    ;
    run;
    quit;
    proc sql noprint;
    select country
    into :listcountry separated by "!"
    from aaa
    ;
    run;
    quit;
    proc sql noprint;
    select place
    into :listplace separated by "!"
    from aaa
    ;
    run;
    quit;
    %let i_1 =1;
    %let i_2 =1;
    %let i_2 =1;
    %do %until (%qscan(&listplace,&i_1,'!') = %str());
             %do %until (%qscan(&listopinion,&i_2,'!') = %str());
                      %do %until (%qscan(&listcountry,&i_3,'!') = %str());                
                          %let zm1=%scan(&listplace,&i_1,'!');
                               find(school,"&zm1")=1 or
                          %let zm2=%scan(&listopinion,&i_2,'!')
                          %let zm3=%scan(&lista3,&i_3,'!');
                          %let i_3=%eval(&i_3+1);
                      %end;
                 %let i_2=%eval(&i_2+1);
             %end;
        %let i_1=%eval(&i_1+1);
    %end;
    run;

Welcome to Stack Overflow and thanks for posting the code that you have. Please edit your question to show code that we can just copy and paste to replicate the issue and please specify what the issue is, e.g., copy an error message from the log, or do you get a different value from what you expected, in which case say what you expected and what you actually got. It should all help. — Amir
See How do I ask a good question to help make improvements to your question. — Amir
No need to increment your loop counters by hand. %do &i_sel1 = 1 %to %sysfunc(countw(&lista1,!));. Do not add quotes where they are not needed. Did you intend to treat either ' or | as delimiters when scanning the list macro variables? Or did you just intend to use | as the only delimiter? — Tom
Do you want to find schools that have an item from both list1 and list2? Or do you just want to find schools that are in either list1 or in list2? If the later then don't nest the %DO loops. If the former then are the lists matched by position? So the first item in list1 is linked to the first item in list2. Or do you want to pair the first item in list1 with every item in list2? — Tom
Maybe more code would be helpful. It looks this is one example on where it is not useful to use macros. — Lee

Tom Tom · Accepted Answer · 2019-11-07T16:13:54

It sounds like for your test of school you want to test if two or more of the names in the list exist in the value of school.

So if your list of schools to find looks like:

%let list=harvard|oxford|wse;

then you want to generate something like:

if sum( 0<findw(school,"harvard",,'i')
      , 0<findw(school,"oxford",,'i')
      , 0<findw(school,"wse",,'i')
      ) >= 2 then do;
...
end;

So you might use something like this:

if sum(

%let sep=;
%do i=1 %to %sysfunc(countw(&list,|));
  &sep  0<findw(school,"%qscan(&list,&i,|)",,'i')
  %let sep=,;
%end;

) >= 2 then do;
...
end;

I have no idea what you want to do with the other two lists. I think you want to use them to generate some SAS code where I have left ....

SAS macros multiple statements

1 Answers