topic Re: Loop through a list of strings in SAS Programming

Loop through a list of strings

xyxu — Fri, 08 May 2020 17:11:57 GMT

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."

Re: Loop through a list of strings

PaigeMiller — Thu, 07 May 2020 23:04:18 GMT

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

Re: Loop through a list of strings

xyxu — Fri, 08 May 2020 00:42:27 GMT

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".

Re: Loop through a list of strings

PaigeMiller — Fri, 08 May 2020 00:53:02 GMT

%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

Re: Loop through a list of strings

RichardDeVen — Fri, 08 May 2020 01:04:54 GMT

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

Re: Loop through a list of strings

xyxu — Fri, 08 May 2020 03:18:20 GMT

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?

Re: Loop through a list of strings

xyxu — Fri, 08 May 2020 04:30:05 GMT

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."

Re: Loop through a list of strings

Shmuel — Fri, 08 May 2020 05:38:50 GMT

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);

Re: Loop through a list of strings

Shmuel — Fri, 08 May 2020 05:41:31 GMT

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

the loop is implicit inside the data step.

Re: Loop through a list of strings

Kurt_Bremser — Fri, 08 May 2020 05:54:07 GMT

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.

Re: Loop through a list of strings

RichardDeVen — Fri, 08 May 2020 06:19:27 GMT

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.

Re: Loop through a list of strings

DavePrinsloo — Fri, 08 May 2020 14:07:23 GMT

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

Re: Loop through a list of strings

xyxu — Fri, 08 May 2020 16:46:31 GMT

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.

Re: Loop through a list of strings

Tom — Fri, 08 May 2020 17:24:10 GMT

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.

Re: Loop through a list of strings

RichardDeVen — Fri, 08 May 2020 18:35:37 GMT

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
  ;