Hope someone can help with a problem I am having. Code below.
My code reads in a job specfile that can contain up to ten keyword filters. These need to be split into their component parts and then passed to a string to build a filter that later gets passed to a where statement.
So if filter1 is CREATIVE CULTURAL INDUSTRY it would be built in a string as follows:-
INDEX(UPCASE(course_long_name),"CREATIVE")>0 AND INDEX(UPCASE(course_long_name),"CULTURAL")>0 AND INDEX(UPCASE(course_long_name),"INDUSTRY")>0.
The problem I am having with my code is that it retains substr_1 but &&substr_&k just resolves to the last macro variable it encounters. Does anyone know how to iteratively pass the macro variables created in &substr_&i to a concatenated string?
%macro splitter(string=,wordpfx=WORD,dlm=%str( ));
%do cnt=1 %to %sysfunc(countw(&string,&dlm));
%global &wordpfx&cnt;
%let &wordpfx&cnt = %scan(&string,&cnt,%str(&dlm));
%* echo macro var result to log window;
%put &wordpfx&cnt=&&&wordpfx&cnt;
%end;
%mend splitter;
%macro quotelist(value, /* list of strings to be quoted */
sep=%NRSTR( ) /* separator character(s) between strings in value */
);
%local s1 s2 v1 v2 out;
%let s1 = %str(%'%");
%let s2 = %str(%"%');
%let v1 = %qsysfunc(translate(&value, &s1., &s2.));
%let v2 = %qsysfunc(tranwrd(%superq(v1), &sep., %nrstr(%" %") ));
%let out = %str(%")&v2.%str(%");
%unquote(&out.)
%mend;
%let filter_1 = CREATIVE INDUSTRIES;
%let filter_2 = CULTURAL INDUSTRIES;
%let filter_3 = CREATIVE AND CULTURAL INDUSTRIES;
%let filter_4 = CREATIVE INDUSTRY;
%let filter_5 = CULTURAL INDUSTRY;
%let filter_6 = CREATIVE AND CULTURAL INDUSTRY;
%let filter_7 = MATH;
%let filter_8=;
%let filter_9=;
%let filter_10=;
%macro loop;
%let string=;
/* up to ten filters can be submitted */
%do i = 1 %to 1;
%if %length(&&filter_&i) %then %do; /* we only want to loop over a filter if it contains a value */
%splitter(string=&&filter_&i,wordpfx=idx,dlm=' ');
%let word_cnt=%sysfunc(countw(&&filter_&i));
%put There are &word_cnt words in the string "&&filter_&i"; /* if the filter has a value then we have split out the keywords into their component parts */
%do j = 1 %to &word_cnt;
%let substr_&j = INDEX(UPCASE(course_long_name),%quotelist(%qsysfunc(compbl(&&idx&j)))) >0;
%put Substr number &j is &&substr_&j;
%end;
%if &word_cnt = 1 %then %do;
%let string&i = &substr_1;
%put STRING IS &&string&i;
%end;
%if &word_cnt >1 %then %do k = 2 %to &word_cnt;
%let string = &substr_1 AND &&substr_&k;
%put STRING IS &string;
%end;
%end;
%end;
%mend loop;
%loop;
Well, I still don't see why filter is in a macro variable, easier to put these type of data elements in a dataset - thats what datasets are for. However per your guidance, this should work:
%let filter_1 = CREATIVE INDUSTRY; data courses; length course_long_name $200; course_long_name="Creative Writing"; output; course_long_name="Creative Industry"; output; course_long_name="Creative and Cultural Industry"; output; course_long_name="Creative Media"; output; run; data want; set courses; found=0; do i=1 to countw("&filter_1."); if index(upcase(course_long_name),scan("&filter_1.",i," ")) > 0 then found=found+1; end; if found=countw("&filter_1.") then result="Found"; run;
You are really fighting the SAS system there with all that macro code. Post an example test dataset for your have (in the form of a datastep) and what you want the output to look like. From what I can gather from you text (as I am not trying to decipher that mass of %'s) you have a file with some values in, you read that in and then generate a proc sql with a where clause on each word. So:
/* Simulate data from file */ data have; length string $200; string="ACURA,AUDI"; output; string="BMW,CADILAC"; output; run; /* Generate necessary code */ data _null_; set have; call execute(cat('proc sql; create table OUT',strip(_n_),' as select * from SASHELP.CARS where upcase(MAKE) in (')); do i=1 to countw(string); if i > 1 then call execute(','); call execute(quote(scan(string,i,','))); end; call execute(');quit;'); run;
This uses simple Base SAS code to create the code necessary to subset sashelp.cars based on each string in the input dataset. So the first row it will create a proc sql, subset where in ("AUCRA","AUDI"), then next creates a proc sql for the next obs and so on.
Yes it is a bit messy isn't it 😕
So sample dataset below:-
data courses;
length course_long_name $200;
course_long_name="Creative Writing"; output;
course_long_name="Creative Industry"; output;
course_long_name="Creative and Cultural Industry"; output;
course_long_name="Creative Media"; output;
run;
%let filter_1 = CREATIVE INDUSTRY;
Resulting output dataset would extract the second and third courses as they contain occurences of the requested filter keywords.
Kim
Well, I still don't see why filter is in a macro variable, easier to put these type of data elements in a dataset - thats what datasets are for. However per your guidance, this should work:
%let filter_1 = CREATIVE INDUSTRY; data courses; length course_long_name $200; course_long_name="Creative Writing"; output; course_long_name="Creative Industry"; output; course_long_name="Creative and Cultural Industry"; output; course_long_name="Creative Media"; output; run; data want; set courses; found=0; do i=1 to countw("&filter_1."); if index(upcase(course_long_name),scan("&filter_1.",i," ")) > 0 then found=found+1; end; if found=countw("&filter_1.") then result="Found"; run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: