topic Re: Wild character question in SAS Procedures

Wild character question

podarum — Wed, 25 Jun 2014 13:13:25 GMT

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

Re: Wild character question

RW9 — Wed, 25 Jun 2014 13:29:19 GMT

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

where APP_ID like '%61738495%';

Re: Wild character question

slchen — Wed, 25 Jun 2014 13:31:47 GMT

Try:

where APP_ID like '%6005617384952%'

Re: Wild character question

podarum — Wed, 25 Jun 2014 13:33:04 GMT

I tried that as well, no avail

Re: Wild character question

Patrick — Wed, 25 Jun 2014 13:39:38 GMT

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

Re: Wild character question

Haikuo — Wed, 25 Jun 2014 13:43:43 GMT

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

Re: Wild character question

Patrick — Wed, 25 Jun 2014 13:46:01 GMT

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

Re: Wild character question

stat_sas — Wed, 25 Jun 2014 13:50:15 GMT

This works fine

data have;

input APP_ID $ 15.;

datalines;

6005617384952

;

proc sql;

select * from have

where APP_ID like '%61738495%';

quit;

Re: Wild character question

Haikuo — Wed, 25 Jun 2014 13:52:16 GMT

"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

Re: Wild character question

Haikuo — Wed, 25 Jun 2014 13:55:21 GMT

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

Re: Wild character question

podarum — Wed, 25 Jun 2014 13:55:27 GMT

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

Re: Wild character question

Patrick — Wed, 25 Jun 2014 13:56:49 GMT

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

Re: Wild character question

RW9 — Wed, 25 Jun 2014 13:59:20 GMT

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.

Re: Wild character question

RafiRahi — Wed, 25 Jun 2014 14:01:46 GMT

The variable APP_ID - is it character variable? If it is then the below code is working in my 9.2 version of base SAS to find any number which has the value '61738495' in the string -

WHERE APP_ID CONTAINS '61738495' ;

If it the above code does not work then please let me know if the variable APP_ID is numeric.

Thanks.

Rafi

Re: Wild character question

podarum — Wed, 25 Jun 2014 14:03:16 GMT

Basically I have a field in my local data that is chopped (eg. '61738495') and am trying to match it to another field in my data warehouse that is not chopped (eg. '6005617384952') . Have been using

on INDEX (TRIM(a.APP_ID_DW), TRIM(put(b.APP_ID_FILE, 12.))) > 0 that didn't work, I get an error like Function TRIM requires a character expression as arguement 1.. Even if I make APP_ID_FILE a character or numeric.

Re: Wild character question

Patrick — Wed, 25 Jun 2014 14:07:56 GMT

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.

Re: Wild character question

podarum — Wed, 25 Jun 2014 14:09:24 GMT

The error I get with CONTAINS is this :

7989: Invalid operand for the CONTAINS operator. The operand must have a Period data type that is comparable to the other operand.

Re: Wild character question

podarum — Wed, 25 Jun 2014 14:11:44 GMT

APP_ID is numeric.

Re: Wild character question

Patrick — Wed, 25 Jun 2014 14:13:53 GMT

So what are the data types of the variables in your 2 tables. They must be character in order for a LIKE or CONTAINS to work.

Re: Wild character question

RW9 — Wed, 25 Jun 2014 14:14:30 GMT

Then you would need to (Oracle sytax again) cast the numeric into a character string to use the like % syntax. In SAS it would be put(variable,best.).