Help using Base SAS procedures

Wild character question

Reply
Super Contributor
Posts: 396

Wild character question

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

Super User
Super User
Posts: 7,413

Re: Wild character question

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

where APP_ID  like '%61738495%';

Super Contributor
Posts: 275

Re: Wild character question

Try:

where APP_ID like '%6005617384952%'

Super Contributor
Posts: 396

Re: Wild character question

I tried that as well, no avail

Respected Advisor
Posts: 3,899

Re: Wild character question

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' .



Respected Advisor
Posts: 3,124

Re: Wild character question

,

"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

Respected Advisor
Posts: 3,899

Re: Wild character question

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...

Respected Advisor
Posts: 3,124

Re: Wild character question

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

Respected Advisor
Posts: 3,899

Re: Wild character question

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

Respected Advisor
Posts: 3,124

Re: Wild character question

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

Respected Advisor
Posts: 3,899

Re: Wild character question

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.

Respected Advisor
Posts: 3,124

Re: Wild character question

Thanks! Learn some!

Trusted Advisor
Posts: 1,204

Re: Wild character question

This works fine

data have;

input APP_ID $ 15.;

datalines;

6005617384952

;

proc sql;

select * from have

where APP_ID like '%61738495%';

quit;

Super Contributor
Posts: 396

Re: Wild character question

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

Super User
Super User
Posts: 7,413

Re: Wild character question

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.

Ask a Question
Discussion stats
  • 28 replies
  • 476 views
  • 1 like
  • 8 in conversation