BookmarkSubscribeRSS Feed
podarum
Quartz | Level 8

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

28 REPLIES 28
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

where APP_ID  like '%61738495%';

slchen
Lapis Lazuli | Level 10

Try:

where APP_ID like '%6005617384952%'

podarum
Quartz | Level 8

I tried that as well, no avail

Patrick
Opal | Level 21

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
Onyx | Level 15

,

"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
Opal | Level 21

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
Onyx | Level 15

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
Opal | Level 21

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

Haikuo
Onyx | Level 15

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
Opal | Level 21

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
Onyx | Level 15

Thanks! Learn some!

stat_sas
Ammonite | Level 13

This works fine

data have;

input APP_ID $ 15.;

datalines;

6005617384952

;

proc sql;

select * from have

where APP_ID like '%61738495%';

quit;

podarum
Quartz | Level 8

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 28 replies
  • 1608 views
  • 1 like
  • 8 in conversation