Help using Base SAS procedures

like/index in proc SQL

Reply
Contributor
Posts: 31

like/index in proc SQL

When pull all the records that contain "SUN" string,
I normally use INDEX(UPCASE(desc), 'SUN') >0 on Proc SQL, but this time, it would NOT work and I have to use UPCASE(desc) LIKE '%SUN%' on Proc SQL to make it work. I just want to know why? Could someone tell me?

Thank you! Ying
PROC Star
Posts: 7,471

Re: like/index in proc SQL

Ying,

Can you provide the forum with an example data step that produces a file where the two proc sqls differ, as well as the full two proc sql calls?

Art
Contributor
Posts: 31

Re: like/index in proc SQL

Thank you!

-- Here is the code with 1,301,674 rows and 1 colunm

proc sql ;
select member_id
from member_promotion_tbl as p
left join
member_offer_tbl as c
on p.offer_id=c.offer_id
where upcase(desc) like '%SUN%';
quit;

-- but 0 rows and 1 colunm if "where index(upcase(desc), 'SUN')>0" was used.

I am not sure why.
PROC Star
Posts: 7,471

Re: like/index in proc SQL

Almost! Can you provide samples of the two files, in the form of a data step, but with only about 10 records each?

Specifically, though, an example where the one code works, but the other doesn't work.

Art
Contributor
Posts: 24

Re: like/index in proc SQL

but I tested both works;
data test;
desc='absunc';
output;
desc='abced';output;
desc='sunac';output;
run;

proc sql;
select * from test where upcase(desc) like '%SUN%';
select * from test where index(upcase(desc),'SUN')>0;
quit;
Super User
Posts: 10,023

Re: like/index in proc SQL

I think the preblom you meet is because the 'desc' is the SAS's key-word,that means sas has special usage for 'desc' ,you will notice that the color of 'desc' would be blue when you input it. You can try to rename 'desc'.
The code below would have the correct result.


[pre]
proc sql;
select *
from sashelp.class
where index(upcase(name),'NE')>0;
quit;
[/pre]


Ksharp
Super Contributor
Super Contributor
Posts: 3,174

Re: like/index in proc SQL

Suggest you post-reply with a SAS log output revealing all SAS code executed and two code-executions where you are not getting output that you expected. Based on personal experience, simply showing SAS statements doesn't provide enough actual evidence.

http://support.sas.com/documentation/cdl/en/sqlproc/62086/HTML/default/a002536894.htm

Scott Barry
SBBWorks, Inc.
Super User
Posts: 10,023

Re: like/index in proc SQL

SBB .
You are right. I actually not test author's code,Just keep my mind free.
Although the author has meet the problem ,I test my code correctly, and it can work.
Maybe there are some other factor to make author wrong.I am not quit sure.:-)
Ask a Question
Discussion stats
  • 7 replies
  • 409 views
  • 0 likes
  • 5 in conversation