Help using Base SAS procedures

Multiple like() statement

Reply
Regular Contributor
Posts: 186

Multiple like() statement

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?

Occasional Contributor
Posts: 17

Re: Multiple like() statement

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

Super User
Posts: 5,079

Re: Multiple like() statement

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?

Regular Contributor
Posts: 186

Re: Multiple like() statement

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!

Occasional Contributor
Posts: 17

Re: Multiple like() statement

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

Regular Contributor
Posts: 186

Re: Multiple like() statement

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;

Occasional Contributor
Posts: 17

Re: Multiple like() statement

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

Regular Contributor
Posts: 186

Re: Multiple like() statement

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;

Occasional Contributor
Posts: 17

Re: Multiple like() statement

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

Regular Contributor
Posts: 186

Re: Multiple like() statement

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?

Occasional Contributor
Posts: 17

Re: Multiple like() statement

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

Regular Contributor
Posts: 186

Re: Multiple like() statement

The count still does not work properly...

Can you please help me fix this?

Occasional Contributor
Posts: 17

Re: Multiple like() statement

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

Regular Contributor
Posts: 186

Re: Multiple like() statement

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!

Ask a Question
Discussion stats
  • 13 replies
  • 425 views
  • 0 likes
  • 3 in conversation