What does the percent symbol at the beginning in a quotation mark mean?

[Phil_NZ]

Hi all SAS Users,

Today I face a code:

proc print data=pg1.np_summary;
	var Type ParkName;
	where ParkName like '%Preserve%';
run;

And the result is

I have some concerns here:

1. I remember inside the single quotation, the word is sensitive, so how come it displays all uppercase in the result report? I know that the percentage symbol represents any character or blank, or else. But how do two percent symbols work in this case? And normally, to me, 'Preserve%' will display some names that should be longer than 'Preserve', so when there are some results like 'PRE' in the result, it is a bit surprising to me?

2. Why when I change the single quotation to double quotation, it displayed a warning? (We all know that single and double quotation can be used interchangeably, except we must use double quotation for macro variables)

42         proc print data=pg1.np_summary;
43         	var Type ParkName;
44         	*Add a WHERE statement;
45         	where ParkName like "'%Preserve%'";
WARNING: Apparent invocation of macro PRESERVE not resolved.
46         run;

Warmest regards and cheers,

[Tom · Accepted solution]

The % is a wildcard for the LIKE operator.  So LIKE '%Preserve%' will match any string that contains Preserve.  Note it will not match PREserve (unless it gets pushed into some remove database that support case insensitive comparison, like Teradata).

You answered the second question yourself.  The % character is a trigger to the macro processor (the people that defined SQL and how the LIKE operator works did not take into consideration that SAS uses the % character for a different meaning).  But the macro processor ignores strings that use single quotes as the encapsulating character.  But when you switch from '%Preserve%' to "%Preverse%" the first % looks like it is a call to some macro named PRESERVE.  The macro processor will ignore the second % because the character after it cannot be the start of a macro name or macro language keyword.

Note that "'%Perserve%'" has two problems.  First it is bounded by double quotes so the macro processor will try to find the %PRESERVE macro.  Second the search pattern now contains the single quotes.  So it will only match values of the variable that also have single quotes on the outside.  In particular the single quotes must be in the first position and the last possible position (based on the length of the variable) so that it is not followed by of the trailing spaces that SAS uses to pad the character variable values to their fixed maximum length. 

[Phil_NZ]

Hi @Tom 

Thank you for your explanation, I am wondering why the result from running the code as above contains these words, it seems to against your explanation.

Please correct me if I get you wrongly?

Thanks in advance.

[SASKiwi]

Your program filters on ParkName not Type.

[Tom]

Because it found the word Preserve.  Check out the green highlights.