Multiple like() statement

[nicnad]

Hi,

I have a table named mytable with a column named object. I want to count the number of records that contains the string test1 test2 or test3

What is the proper way to write this?

[Sylas]

Hi Nicnad,

Following below code will give the count of the records in mytable1 dataset.

Proc sort data=mytable;
by object;
run;

data mytable1(keep=count);
set mytable end=eof;
by object;
retain count;
if _n_ eq 1 then count=0;
if lowcase(strip(object)) in ('test1','test2','test3') then count=count+1;
if eof;
run;

If you want to have all data in the mytable1 remove the word i highlighted in red.

Regards

Sylas.J

[Astounding]

Another possibility:

proc format;

value $findme

'test1', 'test2', 'test3' = 'test1+test2+test3'  other='Other value';

run;

proc freq data=mytable;

   tables object;

   format object $findme.;

run;

Some questions to consider:  Is upper case vs. lower case important?  Does "contains" mean an exact match, or does "contains" include cases where "test1" appears in the middle of the value of OBJECT?

[nicnad]

Thank you both for your the quick replies.

I want my code to be case unsensitive, so I think that using lowcase and expressing the conditions in lower cases is the right way to go.

I want to count object that contains specific value, not an exact match ( I want to count cases where the specific string appear in the middle of the value object).

I would also like the results to be in a distinct table.

Here is my real code, but it does not work. Can you please help me fix it?

data data work.ind14_15_16;
set mylib.test end=eof;
by objet;
retain count_gafi count_sanction count_person;
if _n_ eq 1 then count_gafi=0 count_sanction=0 count_person=0;
if lowcase(strip(objet)) in ("bolivie","équateur","equateur","éthiopie","ethiopie","indonesie","kenya",
"nigeria","nigéria","pakistan", "sao","sri lanka", "thailand", "thailande","tailande","turquie",
"tanzanie","viet","yemen","yémen")
then count_gafi=count_gafi+1;
else if lowcase(strip(objet)) in ("bélarus","belarus", "corée du nord", "rpdc", "coree du nord","ivoire" ,
"congo", "chine" , "cuba" , "érythrée" , "erythree", "iran" , "iraq" , "liberia" , "libéria", "libye" ,
"myanmar", "birmanie", "somalie" , "sierra" , "soudan", "syrie", "zimbabwe")
then count_sanction=count_sanction+1;
else count_person = count_person + 1; /* If the record is not part of count_gafi or count_sanction, I want it to be part of count_person */

if eof;
run;

Thank you for your help and time!

[Sylas]

Hi Nicnad,

Instead of  if lowcase(strip(object))  in code use the below code

if  index(lowcase(strip(object)), " 'bolive','equater'...")  >  0 then count=count+1;

Regards

Sylas.J

[nicnad]

I am not able to make it work... can you please help?

data data work.ind14_15_16;
set mylist.test end=eof;
by objet;
retain count_gafi count_sanction count_person;
if index(lowcase(strip(objet)),"'bolivie','équateur','equateur','éthiopie','ethiopie','indonesie','kenya',
'nigeria','nigéria','pakistan', 'sao','sri lanka', 'thailand', 'thailande','tailande','turquie',
'tanzanie','viet','yemen','yémen'")
then count_gafi=count_gafi+1;
else if index(lowcase(strip(objet)),"'bélarus','belarus', 'corée du nord', 'rpdc', 'coree du nord','ivoire' ,
'congo', 'chine' , 'cuba' , 'érythrée' , 'erythree', 'iran' , 'iraq' , 'liberia' , 'libéria', 'libye' ,
'myanmar', 'birmanie', 'somalie' , 'sierra' , 'soudan', 'syrie', 'zimbabwe'")
then count_sanction=count_sanction+1;
else count_person = count_person + 1; /* If the record is not part of count_gafi or count_sanction, I want it to be part of count_person */

if eof;
run;

[Sylas]

Hi Nicnad,

If you want assign two variables as zero in the following code if _n_ eq 1 then count_gafi=0 count_sanction=0 count_person=0;

You have to go for do statement and place semicolon at end of each variable assignement.

Ex.

if _n_ eq 1 then do;

count_gafi=0;

count_sanction=0;

count_person=0;

end;

Regards

Sylas.J

[nicnad]

Thank you for the replies.

Can you please help me make this piece of code work :

data data work.ind14_15_16;
set mylist.test end=eof;
by objet;
retain count_gafi count_sanction count_person;
if index(lowcase(strip(objet)),"'bolivie','équateur','equateur','éthiopie','ethiopie','indonesie','kenya',
'nigeria','nigéria','pakistan', 'sao','sri lanka', 'thailand', 'thailande','tailande','turquie',
'tanzanie','viet','yemen','yémen'")>0
then count_gafi=count_gafi+1;
else if index(lowcase(strip(objet)),"'bélarus','belarus', 'corée du nord', 'rpdc', 'coree du nord','ivoire' ,
'congo', 'chine' , 'cuba' , 'érythrée' , 'erythree', 'iran' , 'iraq' , 'liberia' , 'libéria', 'libye' ,
'myanmar', 'birmanie', 'somalie' , 'sierra' , 'soudan', 'syrie', 'zimbabwe'")`>0
then count_sanction=count_sanction+1;
else count_person = count_person + 1; /* If the record is not part of count_gafi or count_sanction, I want it to be part of count_person */

if eof;
run;

[Sylas]

Hi Nicnad,

I have highlighted with red font for the changes i have made in the below code, adding that to your code it will work

data data work.ind14_15_16;
set mylist.test end=eof;
by objet;
retain count_gafi count_sanction count_person;

if _n_ eq 1 then do;

     count_gafi=0;

     count_sanction=0;

     count_person=0;

end;
if index(lowcase(strip(objet)),"'bolivie','équateur','equateur','éthiopie','ethiopie','indonesie','kenya',
'nigeria','nigéria','pakistan', 'sao','sri lanka', 'thailand', 'thailande','tailande','turquie',
'tanzanie','viet','yemen','yémen'")
then count_gafi=count_gafi+1;
else if index(lowcase(strip(objet)),"'bélarus','belarus', 'corée du nord', 'rpdc', 'coree du nord','ivoire' ,
'congo', 'chine' , 'cuba' , 'érythrée' , 'erythree', 'iran' , 'iraq' , 'liberia' , 'libéria', 'libye' ,
'myanmar', 'birmanie', 'somalie' , 'sierra' , 'soudan', 'syrie', 'zimbabwe'")
then count_sanction=count_sanction+1;
else count_person = count_person + 1; /* If the record is not part of count_gafi or count_sanction, I want it to be part of count_person */

if eof;
run;

Regards

Sylas.J

[nicnad]

Thank you for your reply.

The count does not work properly it does not count records the contains the given strings... Do you know what might cause this?

[Sylas]

Hi Nicnad,

Add the conditional statement in red font and delete the statement in blue font,

data data work.ind14_15_16;
set mylist.test end=eof;
by objet; /*delete the statement, it might create problem in future if the data is not sorted based on objet*/
retain count_gafi count_sanction count_person;

if _n_ eq 1 then do;

     count_gafi=0;

     count_sanction=0;

     count_person=0;

end;
if index(lowcase(strip(objet)),"'bolivie','équateur','equateur','éthiopie','ethiopie','indonesie','kenya',
'nigeria','nigéria','pakistan', 'sao','sri lanka', 'thailand', 'thailande','tailande','turquie',
'tanzanie','viet','yemen','yémen'") >0
then count_gafi=count_gafi+1;
else if index(lowcase(strip(objet)),"'bélarus','belarus', 'corée du nord', 'rpdc', 'coree du nord','ivoire' ,
'congo', 'chine' , 'cuba' , 'érythrée' , 'erythree', 'iran' , 'iraq' , 'liberia' , 'libéria', 'libye' ,
'myanmar', 'birmanie', 'somalie' , 'sierra' , 'soudan', 'syrie', 'zimbabwe'") >0
then count_sanction=count_sanction+1;
else count_person = count_person + 1; /* If the record is not part of count_gafi or count_sanction, I want it to be part of count_person */

if eof;
run;

Regards

Sylas.J

[nicnad]

The count still does not work properly...

Can you please help me fix this?

[Sylas]

Hi Nicnad,

Remove the statement highlighted in the red font,you will get all the data and more details about the count variable increment. if you face other issue , could you please elaborate.

data data work.ind14_15_16;
set mylist.test end=eof;

retain count_gafi count_sanction count_person;

if _n_ eq 1 then do;

     count_gafi=0;

     count_sanction=0;

     count_person=0;

end;
if index(lowcase(strip(objet)),"'bolivie','équateur','equateur','éthiopie','ethiopie','indonesie','kenya',
'nigeria','nigéria','pakistan', 'sao','sri lanka', 'thailand', 'thailande','tailande','turquie',
'tanzanie','viet','yemen','yémen'") >0
then count_gafi=count_gafi+1;
else if index(lowcase(strip(objet)),"'bélarus','belarus', 'corée du nord', 'rpdc', 'coree du nord','ivoire' ,
'congo', 'chine' , 'cuba' , 'érythrée' , 'erythree', 'iran' , 'iraq' , 'liberia' , 'libéria', 'libye' ,
'myanmar', 'birmanie', 'somalie' , 'sierra' , 'soudan', 'syrie', 'zimbabwe'") >0
then count_sanction=count_sanction+1;
else count_person = count_person + 1; /* If the record is not part of count_gafi or count_sanction, I want it to be part of count_person */

if eof;
run;

Regards

Sylas.J

[nicnad]

Thank you for your help with this.

I am not able to make your code work, but I got it to work in a really poor way.

Here is my code:

data data work.ind14_15_16;
set bkm.alertes_bankmate_conv;
by objet;
retain count_gafi count_sanction count_person ;

switch = "n";

if _n_ eq 1 then do;

     count_gafi=0;

     count_sanction=0;

     count_person=0;
end;

if index(lowcase(strip(objet)),'bolivie')>0 or
index(lowcase(strip(objet)),'équateur')>0 or
index(lowcase(strip(objet)),'equateur')>0 or
index(lowcase(strip(objet)),'éthiopie')>0 or
index(lowcase(strip(objet)),'ethiopie')>0 or
index(lowcase(strip(objet)),'indonésie')>0 or
index(lowcase(strip(objet)),'indonesie')>0 or
index(lowcase(strip(objet)),'kenya')>0 or
index(lowcase(strip(objet)),'nigeria')>0 or
index(lowcase(strip(objet)),'nigéria')>0 or
index(lowcase(strip(objet)),'pakistan')>0 or
index(lowcase(strip(objet)),'sao')>0 or
index(lowcase(strip(objet)),'sri lanka')>0 or
index(lowcase(strip(objet)),'thailand')>0 or
index(lowcase(strip(objet)),'tailand')>0 or
index(lowcase(strip(objet)),'turquie')>0 or
index(lowcase(strip(objet)),'tanzanie')>0 or
index(lowcase(strip(objet)),'viet')>0 or
index(lowcase(strip(objet)),'yemen')>0 or
index(lowcase(strip(objet)),'yémen')>0  then do;
count_gafi=count_gafi+1;
switch = "y";
end;

if index(lowcase(strip(objet)),'bélarus')>0 or
index(lowcase(strip(objet)),'belarus' )>0 or
index(lowcase(strip(objet)),'corée du nord' )>0 or
index(lowcase(strip(objet)),'rpdc' )>0 or
index(lowcase(strip(objet)),'coree du nord')>0 or
index(lowcase(strip(objet)),'ivoire'  )>0 or
index(lowcase(strip(objet)),'congo' )>0 or
index(lowcase(strip(objet)),'chine' )>0 or
index(lowcase(strip(objet)), 'cuba'  )>0 or
index(lowcase(strip(objet)),'érythrée' )>0 or
index(lowcase(strip(objet)), 'erythree')>0 or
index(lowcase(strip(objet)), 'iran'  )>0 or
index(lowcase(strip(objet)),'iraq' )>0 or
index(lowcase(strip(objet)), 'liberia'  )>0 or
index(lowcase(strip(objet)),'libéria' )>0 or
index(lowcase(strip(objet)),'libye'  )>0 or
index(lowcase(strip(objet)),'myanmar' )>0 or
index(lowcase(strip(objet)),'birmanie')>0 or
index(lowcase(strip(objet)), 'somalie'  )>0 or
index(lowcase(strip(objet)),'sierra' )>0 or
index(lowcase(strip(objet)), 'soudan')>0 or
index(lowcase(strip(objet)), 'syrie' )>0 or
index(lowcase(strip(objet)),'zimbabwe')>0
then do;
count_sanction=count_sanction+1;
switch = "y";
end;

if switch ="n" then do;
count_person = count_person + 1; /* If the record is not part of count_gafi or count_sanction, I want it to be part of count_person */
end;

run;

I would like to know what is the most efficient way to write the above and how to create a distinct table only with the count_sanction, count_gafi and count_person.

Thank you for your help and time!