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; ... View more

Hi, Lets say I have the following table : ApplProvenance MontantOperation MontantBilletEspece CO 11000 35000 CO 20000 100 DI 5000 500 IN 100 450 IN 45000 50000 How do I proc freq a table like that : ApplProvenance MontantOperation < 10000 MontantOperation >=10000 MontantBilletEspece < 10000 MontantBilletEspece >= 10000 CO 0 2 1 1 DI 1 0 1 0 IN 1 1 1 1 I tried the following code, but I am not able to make it work : proc format; value montantoperation low-9999='MontantOperation <10000$'     1000-high='MontantOperation >=10000$'; run; proc format; value montantbilletespece low-9999='MontantBilletEspece <10000$'     1000-high='MontantBilletEspece >=10000$'; run; proc freq data = sasuser.impw_0026 ; tables applprovenance montantoperation montantbilletespece / out=test2; format montantoperation montantbilletespece; run; Thank you for your help and time. ... View more

Hi, I have the following code where I want to display records where right(montantoperation,2) = 00 AND left(dateoperation,4) = "2007" How do I write this properly? PROC SQL; CREATE TABLE lctr.TXNS_2007_P_R AS SELECT TXNS.ApplProvenance,   TXNS.DateOperation,   TXNS.TypeCompte,   TXNS.NoTransit,   TXNS.NoCompte,   TXNS.NoGuichet,   TXNS.DateInscription,   TXNS.MontantOperation,   TXNS.CodeDevise,   TXNS.MontantBilletEspece,   TXNS.MontantMonnaie FROM LCTR.TXNS AS TXNS WHERE TXNS.TypeCompte = "1" AND right(TXNS.MontantOperation,2) = 00 AND left(TXNS.DateOperation,4) = "2007"; QUIT; Thank you for your help and time. ... View more

Hi, I would like to know how to store the number of records from a table in a variable and then based on that number select a random sample that is 10% of the size of the total number of records. Here is what I came up with so far. My source table is cq_alert.closed_alert_cust : data _NULL_;   if 0 then set cq_alert.closed_alert_cust nobs=n;   call symput('nrows',trim(left(put(n,8.))));   stop; run; %put nobs= int(&nrows/10); /* I'm trying to store 10% of my total number of records in variable nobs */ To illustrate this better, if my source table had 100 records, I would like to create a table with 10 random records from it. Thank you for your help and time. ... View more