Why doesn't the "contains "yes"" and select subquery produce the same result?

[current_thing]

Hello,

I'm stumped. I have two versions of a query syntax that should produce the same result but it doesn't!

Ver1:

select * from tbl where col1 contains "yes"

Ver2:

select * from tbl where col1 contains (select distinct test from xl.sheet1 where some_col="example")

The subquery (select distinct test from xl.sheet1 where some_col="example") from Ver2 returns "yes".

The problem is that Ver1 returns exactly as expected, but Ver2 returns just a fraction of Ver1. What am I missing here?

[LinusH]

I did a simple test with sashelp.class and got expected result.

Can you post some test data (in form of a data step/datalines) so we can try your exact scenario?

[Tom · Accepted solution]

The issue is trailing spaces.  If the length of TEST is $8 then it returns 'yes     ' and not 'yes'.  So the contains will only match values of COL1 that yes followed by five spaces.

The only way I could get it to work is to add LENGTH= option to force the subquery to generate a value without trailing spaces.

data have;
  string='A ';
run;

proc sql;
select * from sashelp.class where name contains 'A';
%put &=sqlobs;
select * from sashelp.class where name contains (select string from have);
%put &=sqlobs;
select * from sashelp.class where name contains (select string length=1 from have);
%put &=sqlobs;
select * from have where string contains (select string from have);
%put &=sqlobs;
quit;

Also note that the subquery must only return on value.

 89         data have;
 90           input string $ ;
 91         cards;
 
 NOTE: The data set WORK.HAVE has 3 observations and 1 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 
 95         ;
 96         proc sql;
 97         select * from sashelp.class where name contains (select string from have);
 ERROR: Subquery evaluated to more than one row.
 NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
 98         quit;
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: PROCEDURE SQL used (Total process time):
       real time           0.00 seconds
       cpu time            0.01 seconds

[current_thing]

Tom, that worked! Length= in the subquery was the solution!

[Ksharp]

Or using STRIP() function to get rid of these heading/trailing blanks .

data have;
  string='A ';
run;

proc sql;
/*select * from sashelp.class where name contains 'A';*/
select * from sashelp.class where name contains strip((select string from have));
quit;

[Tom]

That solves the problem of the subquery generating a character variable that is longer than the string it contains.  Note that it only works in this case because CONTAINS can only except one value from the subquery.  So there is no conflict with the fact that the STRIP() function can only operate on one value at a time.