BookmarkSubscribeRSS Feed
nicnad
Fluorite | Level 6

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?

13 REPLIES 13
Sylas
Fluorite | Level 6

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
PROC Star

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
Fluorite | Level 6

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
Fluorite | Level 6

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
Fluorite | Level 6

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
Fluorite | Level 6

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
Fluorite | Level 6

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
Fluorite | Level 6

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
Fluorite | Level 6

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
Fluorite | Level 6

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
Fluorite | Level 6

The count still does not work properly...

Can you please help me fix this?

Sylas
Fluorite | Level 6

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
Fluorite | Level 6

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!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 13 replies
  • 1894 views
  • 0 likes
  • 3 in conversation