DATA Step, Macro, Functions and more

Concatenate macro variables within a do loop

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 16
Accepted Solution

Concatenate macro variables within a do loop

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;


Accepted Solutions
Solution
‎06-07-2016 09:42 AM
Super User
Super User
Posts: 7,970

Re: Concatenate macro variables within a do loop

Posted in reply to kimdukes77

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;

View solution in original post


All Replies
Super User
Super User
Posts: 7,970

Re: Concatenate macro variables within a do loop

Posted in reply to kimdukes77

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.

Occasional Contributor
Posts: 16

Re: Concatenate macro variables within a do loop

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

Solution
‎06-07-2016 09:42 AM
Super User
Super User
Posts: 7,970

Re: Concatenate macro variables within a do loop

Posted in reply to kimdukes77

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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 1187 views
  • 1 like
  • 2 in conversation