topic Re: substring in SAS Programming

substring

USHAKS — Thu, 16 Jan 2020 19:18:42 GMT

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'

Re: substring

PaigeMiller — Thu, 16 Jan 2020 19:34:35 GMT

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-data-AKA-generate/ta-p/258712

So, a lot more information is needed here.

Re: substring

USHAKS — Thu, 16 Jan 2020 19:29:51 GMT

Thanks for the help

CHEMDB

MATHDBGS

Re: substring

SASKiwi — Thu, 16 Jan 2020 19:37:39 GMT

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&docsetVersion=9.4&locale=en

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

Re: substring

PaigeMiller — Thu, 16 Jan 2020 19:41:28 GMT

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-data-AKA-generate/ta-p/258712

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

Re: substring

Tom — Thu, 16 Jan 2020 19:53:07 GMT

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;

Re: substring

USHAKS — Thu, 16 Jan 2020 21:26:13 GMT

in the where clause this script worked.

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