Build a repeating statement i n Proc Sql

[deleted_user]

I've got a parameter in a stored process that can have multiple selections, and code that Cynthia suggested is works for building the parameters I then use in subsequent Proc SQL processing. The code is as follows (and yes it's in a sotred process):

%let campuscodescls = "%superq(campuscodes)";
%local i;
%if %superq(campuscodes0) ne
%then %do i=2 %to %superq(campuscodes0);
%let campuscodescls = &campuscodescls,"%superq(campuscodes&i)";
%end;

Here's my question: how can I modify that type of code to instead of picking an entire piece of data in a field, to perform LIKE checking ("If FIELDA LIKE *widget* OR FIELDA LIKE *wadget* or FIELDA LIKE *whatsis* " ?) I can't change my parameter criteria to do this, because the same parameter criteria are also used in other processing.

Thaks to all who answer...looking forward to your replies......

[Cynthia_sas]

You'll have to check with Tech Support on this, but I think that LIKE (as well as CONTAINS) belongs to a WHERE clause or statement and cannot be used with an IF statement.

So depending on the rest of the code you're generating, you may need to generate a WHERE. The statement that has to change is the %LET statement. Since the macro facility is just a big typewriter and is generating text, consider what happens when you add the string WOMBAT to the %let statement:
[pre]
%let campuscodescls = &campuscodescls, WOMBAT "%superq(campuscodes&i)";
[/pre]

The text string WOMBAT would be inserted into whatever was being generated by your macro statements (list of codes) You could even add more than one string to the %LET statement, such as TWAS BRILLIG:
[pre]
%let campuscodescls = &campuscodescls, TWAS BRILLIG "%superq(campuscodes&i)";
[/pre]

OR, more useful to you, you could add the string
REGION LIKE
in order to have the macro facility generate a condition inside the text string, instead of putting nonsense strings there. The macro facility is generating text that will be passed to the compiler. If you test this code in an EG code node
[pre]
options mprint symbolgen mlogic;
ods listing;
%macro doloop;
%global wherecls macvar0 macvar1 macvar2 macvar3;
%do i = 1 %to &macvar0;
%if &i = 1 %then %let wherecls = region LIKE "%superq(macvar&i)";
%else %let wherecls = &wherecls OR region LIKE "%superq(macvar&i)";
%put -------> &i ** &&macvar&i ** wherecls: &wherecls;
%end; /* end do loop */
** now have the whole string for all their selections;

proc print data=sashelp.shoes;
title "using macro generated where clause";
title2 "WHERECLS = %superq(wherecls)";
where &wherecls;
run;
%mend doloop;

%let wherecls=;
%let macvar0 = 3;
%let macvar1 = Asia;
%let macvar2 = Pacific;
%let macvar3 = Canada;
%doloop;
[/pre]
You will see in the LOG what happens for each pass through the %do loop (because of the %PUT statement):
[pre]
-------> 1 ** Asia ** wherecls: region LIKE "Asia"
-------> 2 ** Pacific ** wherecls: region LIKE "Asia" OR region LIKE "Pacific"
-------> 3 ** Canada ** wherecls: region LIKE "Asia" OR region LIKE "Pacific" OR region LIKE "Canada"
[/pre]
The final text string that has been built is:
[pre]
region LIKE "Asia" OR region LIKE "Pacific" OR region LIKE "Canada"
[/pre]
so when I use
where &wherecls;
in the PROC PRINT, what the compiler actually GETS is:
[pre]
WHERE region LIKE "Asia" OR region LIKE "Pacific" OR region LIKE "Canada";
[/pre]
The fact that the output shows observations for the 3 regions shows that the WHERE clause was constructed correctly.

cynthia

[deleted_user]

And again and again, Cynthia, THANK YOU..... for your quick reply and for your willingness to share your knowledge. I'm going to check this out and will let you know how it works on this end.

Thanks again!