topic Re: Concatenate macro variables within a do loop in SAS Programming

Concatenate macro variables within a do loop

kimdukes77 — Tue, 07 Jun 2016 10:43:40 GMT

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;

Re: Concatenate macro variables within a do loop

RW9 — Tue, 07 Jun 2016 10:56:16 GMT

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.

Re: Concatenate macro variables within a do loop

kimdukes77 — Tue, 07 Jun 2016 11:58:34 GMT

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

Re: Concatenate macro variables within a do loop

RW9 — Tue, 07 Jun 2016 12:57:34 GMT

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;