topic Re: like/index in proc SQL in SAS Procedures

like/index in proc SQL

Ying — Sun, 12 Sep 2010 19:25:17 GMT

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

Re: like/index in proc SQL

art297 — Sun, 12 Sep 2010 20:20:32 GMT

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

Re: like/index in proc SQL

Ying — Sun, 12 Sep 2010 20:41:42 GMT

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.

Re: like/index in proc SQL

art297 — Sun, 12 Sep 2010 22:20:07 GMT

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

Re: like/index in proc SQL

Ksharp — Fri, 17 Sep 2010 08:22:36 GMT

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

Re: like/index in proc SQL

sbb — Fri, 17 Sep 2010 13:52:02 GMT

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.

Re: like/index in proc SQL

SUN59338 — Fri, 17 Sep 2010 16:25:06 GMT

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;

Re: like/index in proc SQL

Ksharp — Mon, 20 Sep 2010 06:25:31 GMT

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