06-25-2014 09:13 AM
Is there a reason why this is not getting me any 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
06-25-2014 09:39 AM
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' .
06-25-2014 09:52 AM
06-25-2014 09:56 AM
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...
06-25-2014 09:55 AM
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?
06-25-2014 10:07 AM
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.
06-25-2014 09:59 AM
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.