Loop through a list of strings

[xyxu]

I thought this was easy but got stuck. I have a dataset that is simply a list of strings. My macro needs to loop through this list, each time using one string from the list in a proc. What is an efficient way to make this work?  

_______________________________________________________________________________________________________________________

Please see below for a minimum exampe. I have two datasets (their actual versions are much larger):

data have1;
	input string $6.;
	datalines;
AB C
DEF
G H I
;
run;

data have2;
	input issuer_id 3. security_name $20.;
	datalines;
101 AB C security 05
101 Ab C security 012
103 DEF security 02
104 DEF bond 03
104 Def bond 11
109 G H I security 09
112 G H I bond 04
;
run; 

What I want to do: for each value of string in have1, among all observations in have2 where security_name contains the value, count the number of unique issuer_id:

%macro count_id(in_string);
	proc sql;
		select count(unique issuer_id) as num_id
		from have2 (where = (index(lowcase(security_name), lowcase(&in_string)) >0));			
	quit;
%mend;

I want to put this count back to have1. The below is similar to suggestions by @Kurt_Bremser @RichardDeVen :

data want;
	set have;
	count = dosubl('%count_id(string)');
run;

However, the error message I got is "ERROR: Variable string is not on file WORK.HAVE2."

[PaigeMiller]

Please show us a portion of your data, and then show us the desired results.

[xyxu]

Suppose that I have a dataset as follows:

data have;
	input string $4.;
	datalines;
ABC
DEF
GHI
;
run;

I need a macro to loop through it. In each step, I will perform commands that use one value of string, starting from "ABC". 

[PaigeMiller]

%macro dothis;
Proc sql;
    Select distinct string into :strings separated by ' ' from have;
Quit;
%do i=1 %to %sysfunc(countw(&strings));
    %let thisstring=%scan(&strings,&i,%str( ));
    /* Perform some action using macro variable &thisstring */
%end;
%mend;
%dothis

[xyxu]

I tried to work on this template but it did not work as expected. One potential reason is that my strings include spaces. So for example,

data have;
	input string $10.;
	datalines;
AB C
DEF
G H I
;
run;

Would this code still work?

[Kurt_Bremser]

data _null_;
set have;
call execute(cats('action_part_one',string,'action_part_two'));
run;

How you create the 'action' around the string depends on what the 'action' really is, but you haven't told us that.

[RichardDeVen]

The core essence is that you are submitting code written by some process.

The DOSUBL function is one approach for submitting dynamic code whose essence relies on values in a data set.  In your case the dynamic code is 'the code that invokes a macro'

Example:

data _null_;
  set have;
  sasCode = cats ( '%myMacro(', string, ');' );   /* dynamic code which is source for invoking a macro */
  rc = doSubL (sasCode);
run;

From inside DATA step there are other ways to launch dynamically generated macro invocation code:

• CALL EXECUTE Routine
• RESOLVE Function
• PUT Statements with step followed by %INCLUDE

[xyxu]

This approach looks cool but I wasn't able to feed the value of string in "have" into the macro. I got "ERROR: Variable string is not on file WORK.XXXX."
 

[RichardDeVen]

You have to show the macro source code, or a close facsimile.  A lot depends on what the macro is doing and what the parameters are in the macro definition.

[Shmuel]

that doesn't create a list but a dataset with each string in different observation.

the loop is implicit inside the data step.

[Shmuel]

use next example to loop thru list of strings:

%let row= abc def gehi kl;
%macro loop(line);
   %do i=1 %to %sysfunc(countw(&line));
      %let word = %scan(&line,&i);
      %put word=&word;
   %end;
%mend loop;
%loop(&row);

[DavePrinsloo]

I suggest a minor improvement on the first suggestion:

Don't separate your values blank, but use a tilde ~  or # or any Or use any character that wont show up in your data

Just avoid characters that have meanings in macros, such as quotes,  parenthesis, % &  etc.   

If your strings have such special characters, then you need to mask them.   But I will first assume that they are not an issue.

/* replaced the blank in scan and separarated by with a tilde   ~ 
Or use  any character that wont show up in your data */
%macro dothis;
Proc sql;
    Select distinct string into :strings separated by '~' from have;
Quit;
%do i=1 %to %sysfunc(countw(&strings));
    %let thisstring=%scan(&strings,&i,%str(~));
    /* Perform some action using macro variable &thisstring */
%end;
%mend;
%dothis

[xyxu]

I think another issue is that dataset "have" has more than 1000 rows. So when I run the macro, I got

WARNING: The quoted string currently being processed has become more than 262 bytes
long. You might have unbalanced quotation marks.
ERROR: The function COUNTW referenced by the %SYSFUNC or %QSYSFUNC macro function has
too many arguments.
ERROR: A character operand was found in the %EVAL function or %IF condition where a
numeric operand is required. The condition was: %sysfunc(countw(&names))
ERROR: The %TO value of the %DO I loop is invalid.
ERROR: The macro DOTHIS will stop executing.

[Tom]

At this point it will help for you to show the code you actually tried. 

If %SYSFUNC(COUNT()) is see too many parameters then you gave it unmasked/unquoted/unprotected commas.  Don't do that.

[RichardDeVen · Accepted solution]

Consider a metaphor that information is like wind.  We have new information about the problem and it is time to change tack.

A SQL query will much more cleanly perform the desired counting.

proc sql;
  create table want as
  select 
    string
  , count(distinct issuer_id) as uniq_id_count
  from 
    have1
  join 
    have2
  on 
    lowcase(security_name) like lowcase(cats('%',string,'%'))
  group by
    string
  ;