Count macro based on values in a table

[nicnad]

Hi,

I have a table that contains a variable objet. I want to count the number of records that match certains parameters. Basically I have 5 groups with the following parameters :

• count_gafi_acc : objet needs to contain a specific string (those strings are listed below) and the string 'acc' to be counted
• count_gafi_ref : objet needs to contain a specific string and the string refus to be counted
• count_sanction_acc :  objet needs to contain a specific string and the string 'acc' to be counted
• count_saction_refus :  objet needs to contain a specific string and the string refus to be counted
• count_person : all the records that weren't part of the 4 previous group need to be counted here (in order to achieve that I used switch = "n" and switch = "y" in my code below. I'm sure this isn't the most efficient way to do it.)

Now instead of hard coding the specific string for each group in the code, I would like to create a table named myparameters with two columns. One column called gafi, that contains the specific strings related to the two specific gafi groups and one called sanction related to the two sanction groups.

I would then want to write a sas macro that would count each objet records against each specific parameters in myparameters table and provide the count for each of the 5 groups.

Could you please help me write that macro code?

I have written (with the help of some user of this forum) the following working code. Hope you understand what I am trying to achieve.

Thank you for your help and time.

data mcount;

set mytable;

by objet;

switch = "n";

if _n_ eq 1 then do;

     count_gafi_acc=0;

     count_sanction_acc=0;

     count_gafi_refus=0;

     count_sanction_refus=0;

     count_person=0;

end;

if index(lowcase(strip(objet)),'bolivie')>0 and index(lowcase(strip(objet)),'acc')>0 or

index(lowcase(strip(objet)),'chypre')>0 and index(lowcase(strip(objet)),'acc')>0 or

index(lowcase(strip(objet)),'cyprus')>0 and index(lowcase(strip(objet)),'acc')>0 or

index(lowcase(strip(objet)),'équateur')>0 and index(lowcase(strip(objet)),'acc')>0 or

index(lowcase(strip(objet)),'equateur')>0 and index(lowcase(strip(objet)),'acc')>0 or

index(lowcase(strip(objet)),'éthiopie')>0 and index(lowcase(strip(objet)),'acc')>0 or

index(lowcase(strip(objet)),'ethiopie')>0 and index(lowcase(strip(objet)),'acc')>0 or

index(lowcase(strip(objet)),'indo')>0 and index(lowcase(strip(objet)),'acc')>0 or

index(lowcase(strip(objet)),'kenya')>0 and index(lowcase(strip(objet)),'acc')>0 or

index(lowcase(strip(objet)),'nigeria')>0 and index(lowcase(strip(objet)),'acc')>0 or

index(lowcase(strip(objet)),'nigéria')>0 and index(lowcase(strip(objet)),'acc')>0 or

index(lowcase(strip(objet)),'pakistan')>0 and index(lowcase(strip(objet)),'acc')>0 or

index(lowcase(strip(objet)),'sao')>0 and index(lowcase(strip(objet)),'acc')>0 or

index(lowcase(strip(objet)),'sri lanka')>0 and index(lowcase(strip(objet)),'acc')>0 or

index(lowcase(strip(objet)),'tanzanie')>0 and index(lowcase(strip(objet)),'acc')>0 or

index(lowcase(strip(objet)),'thai')>0 and index(lowcase(strip(objet)),'acc')>0 or

index(lowcase(strip(objet)),'tailand')>0 and index(lowcase(strip(objet)),'acc')>0 or

index(lowcase(strip(objet)),'turquie')>0 and index(lowcase(strip(objet)),'acc')>0 or

index(lowcase(strip(objet)),'viet')>0 and index(lowcase(strip(objet)),'acc')>0 or

index(lowcase(strip(objet)),'yemen')>0 and index(lowcase(strip(objet)),'acc')>0 or

index(lowcase(strip(objet)),'yémen')>0  and index(lowcase(strip(objet)),'acc')>0 then do;

count_gafi_acc=count_gafi_acc+1;

switch = "y";

end;

if index(lowcase(strip(objet)),'bolivie')>0 and index(lowcase(strip(objet)),'refus')>0 or

index(lowcase(strip(objet)),'chypre')>0 and index(lowcase(strip(objet)),'refus')>0 or

index(lowcase(strip(objet)),'cyprus')>0 and index(lowcase(strip(objet)),'refus')>0 or

index(lowcase(strip(objet)),'équateur')>0 and index(lowcase(strip(objet)),'refus')>0 or

index(lowcase(strip(objet)),'equateur')>0 and index(lowcase(strip(objet)),'refus')>0 or

index(lowcase(strip(objet)),'éthiopie')>0 and index(lowcase(strip(objet)),'refus')>0 or

index(lowcase(strip(objet)),'ethiopie')>0 and index(lowcase(strip(objet)),'refus')>0 or

index(lowcase(strip(objet)),'indo')>0 and index(lowcase(strip(objet)),'refus')>0 or

index(lowcase(strip(objet)),'kenya')>0 and index(lowcase(strip(objet)),'refus')>0 or

index(lowcase(strip(objet)),'nigeria')>0 and index(lowcase(strip(objet)),'refus')>0 or

index(lowcase(strip(objet)),'nigéria')>0 and index(lowcase(strip(objet)),'refus')>0 or

index(lowcase(strip(objet)),'pakistan')>0 and index(lowcase(strip(objet)),'refus')>0 or

index(lowcase(strip(objet)),'sao')>0 and index(lowcase(strip(objet)),'refus')>0 or

index(lowcase(strip(objet)),'sri lanka')>0 and index(lowcase(strip(objet)),'refus')>0 or

index(lowcase(strip(objet)),'tanzanie')>0 and index(lowcase(strip(objet)),'refus')>0 or

index(lowcase(strip(objet)),'thai')>0 and index(lowcase(strip(objet)),'refus')>0 or

index(lowcase(strip(objet)),'tailand')>0 and index(lowcase(strip(objet)),'refus')>0 or

index(lowcase(strip(objet)),'turquie')>0 and index(lowcase(strip(objet)),'refus')>0 or

index(lowcase(strip(objet)),'viet')>0 and index(lowcase(strip(objet)),'refus')>0 or

index(lowcase(strip(objet)),'yemen')>0 and index(lowcase(strip(objet)),'refus')>0 or

index(lowcase(strip(objet)),'yémen')>0  and index(lowcase(strip(objet)),'refus')>0 then do;

count_gafi_refus=count_gafi_refus+1;

switch = "y";

end;

if index(lowcase(strip(objet)),'bélarus')>0 and index(lowcase(strip(objet)),'acc')>0 or

index(lowcase(strip(objet)),'belarus' )>0 and index(lowcase(strip(objet)),'acc')>0 or

index(lowcase(strip(objet)),'chine' )>0 and index(lowcase(strip(objet)),'acc')>0 or

index(lowcase(strip(objet)),'congo' )>0 and index(lowcase(strip(objet)),'acc')>0 or

index(lowcase(strip(objet)),'rpdc' )>0 and index(lowcase(strip(objet)),'acc')>0 or

index(lowcase(strip(objet)),'rdc' )>0 and index(lowcase(strip(objet)),'acc')>0 or

index(lowcase(strip(objet)),'coree')>0 and index(lowcase(strip(objet)),'acc')>0 or

index(lowcase(strip(objet)),'corée')>0 and index(lowcase(strip(objet)),'acc')>0 or

index(lowcase(strip(objet)),'ivoire'  )>0 and index(lowcase(strip(objet)),'acc')>0 or

index(lowcase(strip(objet)), 'cuba'  )>0 and index(lowcase(strip(objet)),'acc')>0 or

index(lowcase(strip(objet)),'érythrée' )>0 and index(lowcase(strip(objet)),'acc')>0 or

index(lowcase(strip(objet)), 'erythree')>0 and index(lowcase(strip(objet)),'acc')>0 or

index(lowcase(strip(objet)), 'iran'  )>0 and index(lowcase(strip(objet)),'acc')>0 or

index(lowcase(strip(objet)),'iraq' )>0 and index(lowcase(strip(objet)),'acc')>0 or

index(lowcase(strip(objet)),'irak' )>0 and index(lowcase(strip(objet)),'acc')>0 or

index(lowcase(strip(objet)), 'liban'  )>0 and index(lowcase(strip(objet)),'acc')>0 or

index(lowcase(strip(objet)), 'lebanon'  )>0 and index(lowcase(strip(objet)),'acc')>0 or

index(lowcase(strip(objet)), 'liberia'  )>0 and index(lowcase(strip(objet)),'acc')>0 or

index(lowcase(strip(objet)),'libéria' )>0 and index(lowcase(strip(objet)),'acc')>0 or

index(lowcase(strip(objet)),'libye'  )>0 and index(lowcase(strip(objet)),'acc')>0 or

index(lowcase(strip(objet)),'lybie'  )>0 and index(lowcase(strip(objet)),'acc')>0 or

index(lowcase(strip(objet)),'lybia'  )>0 and index(lowcase(strip(objet)),'acc')>0 or

index(lowcase(strip(objet)),'myanmar' )>0 and index(lowcase(strip(objet)),'acc')>0 or

index(lowcase(strip(objet)),'birmanie')>0 and index(lowcase(strip(objet)),'acc')>0 or

index(lowcase(strip(objet)),'sierra' )>0 and index(lowcase(strip(objet)),'acc')>0 or

index(lowcase(strip(objet)), 'somalie'  )>0 and index(lowcase(strip(objet)),'acc')>0 or

index(lowcase(strip(objet)), 'somalia'  )>0 and index(lowcase(strip(objet)),'acc')>0 or

index(lowcase(strip(objet)), 'soudan')>0 and index(lowcase(strip(objet)),'acc')>0 or

index(lowcase(strip(objet)), 'sudan')>0 and index(lowcase(strip(objet)),'acc')>0 or

index(lowcase(strip(objet)), 'syrie' )>0 and index(lowcase(strip(objet)),'acc')>0 or

index(lowcase(strip(objet)),'zimbabwe')>0 and index(lowcase(strip(objet)),'acc')>0

then do;

count_sanction_acc=count_sanction_acc+1;

switch = "y";

end;

if index(lowcase(strip(objet)),'bélarus')>0 and index(lowcase(strip(objet)),'refus')>0 or

index(lowcase(strip(objet)),'belarus' )>0 and index(lowcase(strip(objet)),'refus')>0 or

index(lowcase(strip(objet)),'chine' )>0 and index(lowcase(strip(objet)),'refus')>0 or

index(lowcase(strip(objet)),'congo' )>0 and index(lowcase(strip(objet)),'refus')>0 or

index(lowcase(strip(objet)),'rpdc' )>0 and index(lowcase(strip(objet)),'refus')>0 or

index(lowcase(strip(objet)),'rdc' )>0 and index(lowcase(strip(objet)),'refus')>0 or

index(lowcase(strip(objet)),'coree')>0 and index(lowcase(strip(objet)),'refus')>0 or

index(lowcase(strip(objet)),'corée')>0 and index(lowcase(strip(objet)),'refus')>0 or

index(lowcase(strip(objet)),'ivoire'  )>0 and index(lowcase(strip(objet)),'refus')>0 or

index(lowcase(strip(objet)), 'cuba'  )>0 and index(lowcase(strip(objet)),'refus')>0 or

index(lowcase(strip(objet)),'érythrée' )>0 and index(lowcase(strip(objet)),'refus')>0 or

index(lowcase(strip(objet)), 'erythree')>0 and index(lowcase(strip(objet)),'refus')>0 or

index(lowcase(strip(objet)), 'iran'  )>0 and index(lowcase(strip(objet)),'refus')>0 or

index(lowcase(strip(objet)),'iraq' )>0 and index(lowcase(strip(objet)),'refus')>0 or

index(lowcase(strip(objet)),'irak' )>0 and index(lowcase(strip(objet)),'refus')>0 or

index(lowcase(strip(objet)), 'liban'  )>0 and index(lowcase(strip(objet)),'refus')>0 or

index(lowcase(strip(objet)), 'lebanon'  )>0 and index(lowcase(strip(objet)),'refus')>0 or

index(lowcase(strip(objet)), 'liberia'  )>0 and index(lowcase(strip(objet)),'refus')>0 or

index(lowcase(strip(objet)),'libéria' )>0 and index(lowcase(strip(objet)),'refus')>0 or

index(lowcase(strip(objet)),'libye'  )>0 and index(lowcase(strip(objet)),'refus')>0 or

index(lowcase(strip(objet)),'lybie'  )>0 and index(lowcase(strip(objet)),'refus')>0 or

index(lowcase(strip(objet)),'lybia'  )>0 and index(lowcase(strip(objet)),'refus')>0 or

index(lowcase(strip(objet)),'myanmar' )>0 and index(lowcase(strip(objet)),'refus')>0 or

index(lowcase(strip(objet)),'birmanie')>0 and index(lowcase(strip(objet)),'refus')>0 or

index(lowcase(strip(objet)),'sierra' )>0 and index(lowcase(strip(objet)),'refus')>0 or

index(lowcase(strip(objet)), 'somalie'  )>0 and index(lowcase(strip(objet)),'refus')>0 or

index(lowcase(strip(objet)), 'somalia'  )>0 and index(lowcase(strip(objet)),'refus')>0 or

index(lowcase(strip(objet)), 'soudan')>0 and index(lowcase(strip(objet)),'refus')>0 or

index(lowcase(strip(objet)), 'sudan')>0 and index(lowcase(strip(objet)),'refus')>0 or

index(lowcase(strip(objet)), 'syrie' )>0 and index(lowcase(strip(objet)),'refus')>0 or

index(lowcase(strip(objet)),'zimbabwe')>0 and index(lowcase(strip(objet)),'refus')>0

then do;

count_sanction_refus=count_sanction_refus+1;

switch = "y";

end;

if switch ="n" then do;

count_person = count_person + 1; /* If the record is not part of count_gafi_acc, count_gafi_refus, count_sanction_acc or count_sanction_refus , I want it to be part of count_person */

end;

if eof;

run;

[Ron_MacroMaven]

Your code can be made more readable by consolidating the multiple function calls:

if (	index(lowcase(strip(objet)),'bolivie')
	or index(lowcase(strip(objet)),'chypre')
	or index(lowcase(strip(objet)),'cyprus')
	or index(lowcase(strip(objet)),'équateur')
	or index(lowcase(strip(objet)),'equateur')
	or index(lowcase(strip(objet)),'éthiopie')
	or index(lowcase(strip(objet)),'ethiopie')
	or index(lowcase(strip(objet)),'indo')
	or index(lowcase(strip(objet)),'kenya')
	or index(lowcase(strip(objet)),'nigeria')
	or index(lowcase(strip(objet)),'nigéria')
	or index(lowcase(strip(objet)),'pakistan')
	or index(lowcase(strip(objet)),'sao')
	or index(lowcase(strip(objet)),'sri lanka')
	or index(lowcase(strip(objet)),'tanzanie')
	or index(lowcase(strip(objet)),'thai')
	or index(lowcase(strip(objet)),'tailand')
	or index(lowcase(strip(objet)),'turquie')
	or index(lowcase(strip(objet)),'viet')
	or index(lowcase(strip(objet)),'yemen')
	or index(lowcase(strip(objet)),'yémen')

   ) then do;

   if index(lowcase(strip(objet)),'acc') then do;

	count_gafi_acc=count_gafi_acc+1;
	switch = "y";
	end;

   else

   if index(lowcase(strip(objet)),'refus') then do;

	count_gafi_refus=count_gafi_refus+1;
	switch = "y";
	end;

   end;

Ron Fehd  logical simplicity: know redundancy maven

[data_null__]

Repace INDEX with FIND it has options to ignore case and trim the arguments.

[nicnad]

Thank you both for your replies.

This will help me make my code more efficient, but what I really want to achieve is to create two tables and search whitin those table, so that my parameters won't be hardcoded in my code and I will be able to modify the table with ease. Can you please help me write that macro code?

The two tables would be the following :

The first row in bold is the header (name of the variable). The table could be named mygafi and mysanction

GAFI

bolivie

chypre

cyprus

équateur

equateur

éthiopie

ethiopie

indo

kenya

nigeria

nigéria

pakistan

sao

lanka

tanzanie

thai

tailand

turquie

viet

yemen

yémen

SANCTION

bélarus

belarus

chine

congo

rpdc

rdc

coree

corée

ivoire

cuba

érythrée

erythree

iran

iraq

irak

liban

lebanon

liberia

libéria

libye

lybie

lybia

myanmar

birmanie

sierra

somalie

somalia

soudan

sudan

syrie

zimbabwe

[Ron_MacroMaven]

Your list==control data set does not contain values that are matched as parameter pairs

GAFI

bolivie

SANCTION

bélarus

in other words for list processing

the values in row.1 are the parameters to a macro call

the values in row.2 are the parameters to a macro call

...

the values in row.N are the parameters to a macro call

if you create two control data sets

MyGafi

and

MySantion(s)

then you can modify my macros above to the following:

%is_in(category=refus

      ,data=MyGafi

      )

%CallText(data = &Data

   ,MacroText =

or index(lowcase(strip(objet)),"&Gafi")

   )

Here is the page with the Macro CallText:

http://www.sascommunity.org/wiki/Macro_CallText

Ron Fehd  list processing maven

[nicnad]

Thank you for your replies.

I'm kind of lost right now. Sorry I'm a real beginner with SAS coding.

Let me describe what I want to do.

#1 I create two tables containing a list of different countries :

data bkm.gafi_countries;

input gafi $20.;

cards;

bolivie

chypre

cyprus

équateur

equateur

éthiopie

ethiopie

indo

kenya

nigeria

nigéria

pakistan

sao

lanka

tanzanie

thai

tailand

turquie

viet

yemen

yémen

;run;

data bkm.sanction_countries;

input sanction $20.;

cards;

bélarus

belarus

chine

congo

rpdc

rdc

coree

corée

ivoire

cuba

érythrée

erythree

iran

iraq

irak

liban

lebanon

liberia

libéria

libye

lybie

lybia

myanmar

birmanie

sierra

somalie

somalia

soudan

sudan

syrie

zimbabwe

;run;

#2 I have an existing table called alertes_pays in which there is a variable called objet. I want to go through each observation in the table alertes_pays and make the 5 following counts. Those counts divided in 3 categories.

• Match the variable objet for each record in the table alertes_pays with the table gafi_countries
  • If the variable objet contains a string from the table gafi_countries and the string 'acc' then count_gafi_acc = count_gafi_acc + 1
  • If the variable objet contains a string from the table gafi_countries and the string 'refus' then count_gafi_refus = count_gafi_refus + 1

• Match the variable objet for each record in the table alertes_pays with the table gafi_countries
  • If the variable objet contains a string from the table sanction_countries and the string 'acc' then count_sanction_acc =        count_sanction_acc + 1
  • If the variable objet contains a string from the table sanction_countries and the string 'refus' then count_sanction_refus = count_sanction_refus + 1
• If the variable objet for each record in the table alertes_pays was not matched in the two previous categories then count_person = count_person + 1

#3 The end result sould be a table with 1 row and 5 columns where each of the column represent the value of the count variable created above.

I know your previous replies were quite close to this, but I am unable to get there even when reading the %calltext and list help file...

I'm really thankful for your help.

Best regards,

nicnad

[Ron_MacroMaven]

I believe this will do what you want:

%macro is_in(category=

            ,list=

            ,two_words=

            ,testing=0

            );

%let testing = %eval(&Testing or %sysfunc(getoption(mprint)) eq MPRINT);

%local I;

if 0

   %do I = 1 %to %sysfunc(countw(&List,%str( )));

       %let item = %scan(&List,&I,%str( ));

       %if &Testing %then %put note: item:&Item;

       or index(lowcase(strip(objet)),"&Item")

       %end;

   %if %length(&Two_Words) %then %do;

       or index(lowcase(strip(objet)),"&Two_Words")

       %end;     

   then do;

   if index(lowcase(strip(objet)),"&Category") then do;

      count_sanction_&Category=count_sanction_&Category+1;

      switch = "y";

      end;

   end; 

%mend; 

%is_in(category=acc

      ,list=

bolivie 

chypre   

cyprus   

équateur

equateur

éthiopie

ethiopie

indo    

kenya   

nigeria 

nigéria 

pakistan

sao     

tanzanie

thai    

tailand 

turquie 

viet    

yemen   

yémen

,two_words=srilanka

)

%is_in(category=refus

      ,list=

      )

This technique of scanning a list for each item

is shown in

http://www.sascommunity.org/wiki/List_Processing_Basics_Creating_and_Using_Lists_of_Macro_Variables

Ron Fehd  list processing maven

[nicnad]

Oops. I posted my reply at the same time you replied.

I think this achieves what I want to do. I will give it a try a let know.

Thank you very much for your help and time!

[Ron_MacroMaven]

sleeping on a problem always help me realize easier ways to do things

keyword: wallpaper code

Here is my this.morning's version of a solution using temporary arrays to replace the wallpaper

array Country{*}$10 _temporary_

     ('bolivie'

      'chypre'

      'cyprus'

      'équateur'

      'equateur'

      'éthiopie'

      'ethiopie'

      'indo'

      'kenya'

      'nigeria'

      'nigéria'

      'pakistan'

      'sao'

      'sri lanka'

      'tanzanie'

      'thai'

      'tailand'

      'turquie'

      'viet'

      'yemen'

      'yémen'

      );

Have_Country = 0;

do I = 1 to dim(Country);

   Have_Country = Have_Country

               or index(lowcase(strip(objet)),Have_Country(I));

   end;

if Have_Country

   and index(lowcase(strip(objet)),'acc') then do;

   count_gafi_acc=count_gafi_acc+1;

   switch = "y";

   end;

[nicnad]

To make this more clear, this is how I would write it in VBA if each tables were defined as range in an Excel sheet, The thing is I don't quite understand the loop For each in SAS. Hope the following makes it clearer for you guys :

Sub test()

Dim checkcountry As Boolean

Set alertes_pays = Range("Alertes_pays")

Set gafi_countries = Range("Gafi_countries")

Set sanction_countries = Range("Sanction_countries")

    For Each records In alertes_pays ' *** represent the table alertes_pays which contains objet variable

    records = Trim(LCase(records))

    checkcountry = False

        For Each countries In gafi_countries ' *** represent the table gafi_countries

            If InStr(records, countries) > 0 Then

                checkcountry = True

                If InStr(records, "acc") > 0 Then count_gafi_acc = count_gafi_acc + 1

                If InStr(records, "refus") > 0 Then count_gafi_refus = count_gafi_refus + 1

            End If

        Next countries

        For Each countries In sanction_countries ' *** represent the table sanction_countries

            If InStr(records, countries) > 0 Then

            checkcountry = True

                If InStr(records, "acc") > 0 Then count_sanction_acc = count_sanction_acc + 1

                If InStr(records, "refus") > 0 Then count_sanction_refus = count_sanction_refus + 1

            End If

        Next countries

        If checkcountry = False Then count_person = count_person + 1

    Next records

Sheets("RESULT").Cells(1, 1) = count_gafi_acc

Sheets("RESULT").Cells(1, 2) = count_gafi_refus

Sheets("RESULT").Cells(1, 3) = count_sanction_acc

Sheets("RESULT").Cells(1, 4) = count_sanction_refus

Sheets("RESULT").Cells(1, 5) = count_person

End Sub

[Haikuo]

If you could post some sample piece of your dataset 'alertes_pays', I would have tested the following code for you.  two lookup table have been loaded into memory by hash, and for each record in  ' alertes_pays', it will loop through both of them, check if the conditions met.

You can also choose to use temporary array(), then do the same loop.

data want;

  if _n_=1 then do;

  if 0 then set bkm.gafi_countries bkm.sanction_countries;

declare hash g(dataset:'bkm.gafi_countries');

g.definekey('gafi');

g.definedone();

declare hiter hig('g');

declare hash s(dataset:'bkm.sanction_countries');

  s.definekey('sanction');

s.definedone();

declare hiter his('s');

end;

Haikuo

set bkm.alertes_pays end=last;

  _temp=sum(count_gafi_acc,count_gafi_refus, count_sanction_acc, count_sanction_refus);

  do rc=hig.first() by 0 while (rc=0);

if find(objet, gafi, 'i') and find(objet, 'acc','i') then count_gafi_acc+1;

  if find(objet, gafi, 'i') and find(objet, 'refus','i') then count_gafi_refus+1;

  rc=hig.next();

end;

do rc=his.first() by 0 while (rc=0);

  if find(objet, sanction, 'i') and find(objet, 'acc','i') then count_sanction_acc+1;

  if find(objet, sanction, 'i') and find(objet, 'refus','i') then count_sanction_refus+1;

rc=his.next();

end;

if _temp >= sum(count_gafi_acc,count_gafi_refus, count_sanction_acc, count_sanction_refus) then count_person+1;

if last then output;

keep count_gafi_acc count_gafi_refus count_sanction_acc count_sanction_refus count_person;

run;

Haikuo

[nicnad]

Works like a charm thank you very much.

[nicnad]

After review the count I am getting is 0 for count_gafi_acc,count_gafi_refus, count_sanction_acc, count_sanction_refus.

It seems like the part where we use the variable gafi here :

if find(objet, gafi, 'i') and find(objet, 'acc','i') then count_gafi_acc+1;

or variable sanction here :

if find(objet, sanction, 'i') and find(objet, 'acc','i') then count_sanction_acc+1;

Is wrong. I don't know much about hash key. Could you please help me solve this?

[Haikuo]

You gotta give some sample data (preferably with some positive hits) for me to work on. To trouble shoot, I need to see the data and the log. I can't guess it all the way through.

Haikuo

[nicnad]

Here is an exemple :

data gafi_countries;

input gafi $20.;

cards;

bolivie

chypre

cyprus

équateur

equateur

éthiopie

ethiopie

indo

kenya

nigeria

nigéria

pakistan

sao

lanka

tanzanie

thai

tailand

turquie

viet

yemen

yémen

;run;

data sanction_countries;

input sanction $20.;

cards;

bélarus

belarus

chine

congo

rpdc

rdc

coree

corée

ivoire

cuba

érythrée

erythree

iran

iraq

irak

liban

lebanon

liberia

libéria

libye

lybie

lybia

myanmar

birmanie

sierra

somalie

somalia

soudan

sudan

syrie

zimbabwe

;run;

data mytest;

input objet $20.;

cards;

belarus acc

belarus refus

somalia acc

somalia refus

bolivie acc

bolivie refus

chypre acc

chypre refus

john

bob

;

data want;

  if _n_=1 then do;

  if 0 then set bkm.gafi_countries bkm.sanction_countries;

declare hash g(dataset:'bkm.gafi_countries');

g.definekey('gafi');

g.definedone();

declare hiter hig('g');

declare hash s(dataset:'bkm.sanction_countries');

  s.definekey('sanction');

s.definedone();

declare hiter his('s');

end;

set mytest end=last;

  _temp=sum(count_gafi_acc,count_gafi_refus, count_sanction_acc, count_sanction_refus);

  do rc=hig.first() by 0 while (rc=0);

if find(objet, gafi, 'i') and find(objet, 'acc','i') then count_gafi_acc+1;

  if find(objet, gafi, 'i') and find(objet, 'refus','i') then count_gafi_refus+1;

  rc=hig.next();

end;

do rc=his.first() by 0 while (rc=0);

  if find(objet, sanction, 'i') and find(objet, 'acc','i') then count_sanction_acc+1;

  if find(objet, sanction, 'i') and find(objet, 'refus','i') then count_sanction_refus+1;

rc=his.next();

end;

if _temp >= sum(count_gafi_acc,count_gafi_refus, count_sanction_acc, count_sanction_refus) then count_person+1;

if last then output;

keep count_gafi_acc count_gafi_refus count_sanction_acc count_sanction_refus count_person;

run;

This gives count_person = 10 all other variables = 0

and should give count_gafi acc = 2 count_gafi_refus = 2 count_sanction_acc = 2 count_sanction_refus = 2 count_person = 2

Thank you very much for your help and time with this