like/index in proc SQL

[Ying]

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

[art297]

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

[Ying]

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.

[art297]

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

[SUN59338]

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;

[Ksharp]

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

[sbb]

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.

[Ksharp]

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