topic Re: Count macro based on values in a table in SAS Programming

Count macro based on values in a table

nicnad — Fri, 12 Apr 2013 18:51:54 GMT

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;

Re: Count macro based on values in a table

Ron_MacroMaven — Fri, 12 Apr 2013 19:41:04 GMT

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

Re: Count macro based on values in a table

data_null__ — Fri, 12 Apr 2013 20:01:24 GMT

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

Re: Count macro based on values in a table

Ron_MacroMaven — Fri, 12 Apr 2013 20:15:35 GMT

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;

%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

Re: Count macro based on values in a table

nicnad — Fri, 12 Apr 2013 20:16:07 GMT

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

Re: Count macro based on values in a table

nicnad — Fri, 12 Apr 2013 20:19:31 GMT

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!

Re: Count macro based on values in a table

Ron_MacroMaven — Fri, 12 Apr 2013 20:27:17 GMT

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

Re: Count macro based on values in a table

nicnad — Fri, 12 Apr 2013 21:06:04 GMT

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

Re: Count macro based on values in a table

nicnad — Fri, 12 Apr 2013 21:57:44 GMT

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

Re: Count macro based on values in a table

Haikuo — Fri, 12 Apr 2013 22:09:04 GMT

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

Re: Count macro based on values in a table

nicnad — Fri, 12 Apr 2013 22:24:49 GMT

Works like a charm thank you very much.

Re: Count macro based on values in a table

Ron_MacroMaven — Sat, 13 Apr 2013 13:56:46 GMT

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;

Re: Count macro based on values in a table

nicnad — Mon, 15 Apr 2013 17:42:59 GMT

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?

Re: Count macro based on values in a table

Haikuo — Mon, 15 Apr 2013 17:52:21 GMT

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

Re: Count macro based on values in a table

nicnad — Mon, 15 Apr 2013 18:04:09 GMT

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

Re: Count macro based on values in a table

Haikuo — Mon, 15 Apr 2013 18:28:10 GMT

There you go! The key is applying strip() to remove blanks (mainly trailing blanks here):

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

;

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

;

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 gafi_countries sanction_countries;

declare hash g(dataset:'gafi_countries');

g.definekey('gafi');

g.definedone();

declare hiter hig('g');

declare hash s(dataset:'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, strip(gafi), 'i') and find(objet, 'acc','i') then count_gafi_acc+1;

if find(objet, strip(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, strip(sanction), 'i') and find(objet, 'acc','i') then count_sanction_acc+1;

if find(objet, strip(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

Re: Count macro based on values in a table

nicnad — Mon, 15 Apr 2013 18:40:15 GMT

This works perfectly!

Thank you very much for your help and time.