substring

[USHAKS]

I want to give a filter on columnOne in the where clause

I use the following code in SQL and I get what i want

but when I write the same code in proc sql there is no errors but my code is not returning any rows?

Please help!

where year =2019

and right(left(ColumnOne, 6), 2) = 'DB'

[PaigeMiller]

It would help to see a portion of the actual data of variable ColumnOne. Otherwise, how could we be sure what the problem is? We also need to see the YEAR variable.

Please follow these instructions to create SAS data step code for your data, please provide a portion of your data this way and not via any other method: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

So, a lot more information is needed here.

[USHAKS]

Thanks for the help

CHEMDB

MATHDBGS

[PaigeMiller]

We also need to see YEAR.

Please show this to us as a SAS data step, following these isntructions: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

Or perhaps show us the output of PROC CONTENTS, that will give us a clue.

[SASKiwi]

Not sure what you are trying to do here but the LEFT and RIGHT functions in SAS only have one parameter, not two as in your example: https://documentation.sas.com/?docsetId=ds2ref&docsetTarget=n1j1jxao7prdsmn1f9hrjtkwc6ue.htm&docsetV...

Maybe - substr(ColumnOne, 5, 2) = 'DB';

[Tom · Accepted solution]

Are you just trying to test if the value end with the letters DB?

where year =2019
  and ColumnOne like '%DB'

If you really had a need to extract the last two characters from a string then use the function mentioned in your subject line.

data want;
  set have ;
  last2 = substrn(ColumnOne,length(ColumnOne)-1));
run;

[USHAKS]

in the where clause this script worked.

AND( substr(ColumnOne,5,2) ='DB' OR ColumnOne like '%DB' )