Wild character question

[podarum]

Is there a reason why this is not getting me any data ?..

Select  a.*

from Mainframe.data

where APP_ID = ('%61738495%')

This is just a test on one account, but I need to do it for 1000's of accounts and in the above case the actual APP_ID is 6005617384952 , I have a file with the acct number stripped in the middle (as shown above), and need to make sure I capture it as it is embedded in the mainframe data.  When I run the above query I get no results.  If I put

where APP_ID = ('%6005617384952%'), I get the result, but that doesn't help, since I don't know the full acct number for all 1000's accts.  Isn't the meaning of the % to capture any possible value in it's place, if you don't know it?  Thanks

[RW9]

Yep, you want to change your equals sign to a like:

where APP_ID  like '%61738495%';

[slchen]

Try:

where APP_ID like '%6005617384952%'

[podarum]

I tried that as well, no avail

[Patrick]

In SQL the '%' is the placeholder for 0 to n characters.

The syntax you've posted   where APP_ID = ('%61738495%') worked for me in a SAS9.4 Windows 7 environment.

Sorry, looked at the wrong table. The '=' sign looks for an exact match so there you won't get a result. You need to use a 'LIKE', so  where APP_ID like '%61738495%'

It's now a rather long time ago but I've used such syntax in the past many many times in a SAS Mainframe environment and it always returned the expected result.

Are you running your code against a SAS dataset or against a database table?

Alternative syntax you could try: where APP_ID =: '61738495' or where APP_ID contains '61738495' .

[Haikuo]

,

"Alternative syntax you could try: where APP_ID =: '61738495' "

If my memory servers me right, you can't do that. Colon modifier =: is only good for data step, it is not supported in Proc SQL.

Regards,

Haikuo

[Patrick]

Your memory works perfect.

It was me messing up my testing - still had proc printto active directing my log to somewhere else after answering another post. Hmmm...

[Haikuo]

I don't know if you are pass-thru, if not,  Would you be able to use SAS function, such as "where find(APP_ID, '61738495') >0" ?

Haikuo

[Patrick]

I believe a "find()" couldn't get passed to the database but it should work with a "contains".

[Haikuo]

True. Correct me if I am wrong, but if not "pass-thru", meaning there is a libname statement in action, then it seems to me that the data is first pulled into SAS, then SAS functions is supported?

Haikuo

[Patrick]

If the data lives in a database the SAS access engine will try and push processing to the database. This is also true if you're using implicit pass-through meaning that you define a libname with the access engine and then use the table in a SAS SQL flavour.

You then can find for every SAS Access engine and version a list of SAS functions documented which can get pushed to the database - and using options sastrace=",,,d" sastraceloc=saslog nostsuffix; then shows you in the log hos SAS tries to convert the SAS SQL flavor to database SQL flavor and if the database could deal with the sent SQL.

[Haikuo]

Thanks! Learn some!

[stat_sas]

This works fine

data have;

input APP_ID $ 15.;

datalines;

6005617384952

;

proc sql;

select * from have

where APP_ID like '%61738495%';

quit;

[podarum]

To test all this I'm using Teradata SQL .. could that be the reason ?

[RW9]

It shouldn't make much difference the SQL engine in questions as:

where APP_ID  like '%61738495%';

Is basic SQL syntax.  Did you try that and are still getting no results, have you looked at the data on the database/run the SQL through a tool designed for SQL, e.g. (and this is for Oracle but hey) Toad, and seeing what is returned.  Do you get results if you remove the where clause.  If you get nothing without a where clause you may be lacking read access, or missing connection, if you do get results then maybe the number is not found.