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."
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 ;
Please show us a portion of your data, and then show us the desired results.
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".
%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
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?
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.
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:
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."
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.
that doesn't create a list but a dataset with each string in different observation.
the loop is implicit inside the data step.
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);
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
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.
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.
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 ;
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.