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 :
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;
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
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
Repace INDEX with FIND it has options to ignore case and trim the arguments.
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 |
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
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.
#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
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
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!
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;
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
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
Works like a charm thank you very much.
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?
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
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.