For this specific table, it is not working. The result is blank. But I tried it on smaller tables and it works. ... View more

@nicnad, Quote: "First off hai.kuo solution does not seem to remove duplicate criteria_value when concatenating." It was fine on my SAS 9.3,. Windows 7 64bit, Hash() remove dups by default, for the raw input as is, the code produces exact output you required,  unless you mean something else otherwise? So please post the part of data that the code did not work. Quote: "Also, the columns aren't sorted. My real data has thirty criteriaid and it goes c13,c12,c1,c2 and so on... I would like the column to be sorted." Again, Hash() sort your data by default, you raw data does not need to be presorted, and your outcome table will be sorted. Hash() solution tackles your two concerns dead-on, now you make me confused. Haikuo Update: I think I have figured out your first concern, it can be easily fixed, but I could be wrong that if you mean something else. data have1;   input ClientId $      CriteriaId  Criteria_Value$;   cards; aaabbb      1 No 12345 2     No 12345 3     John 6789  1 Yes 6789  1 No 6789  1 Yes aaabbb      1 Yes aaabbb      2 Yes 12345 1     Yes ; /*Step1: Using Hash() to prep the data, this is the key step*/ data _null_;   if _n_=1 then do;    length Criteria_Value $50;      if 0 then set have1;        declare hash h(ordered:'a');        h.definekey('ClientId' , 'CriteriaId');        h.definedata('ClientId' , 'CriteriaId', 'Criteria_Value');        h.definedone();    end;    set have1(rename=(Criteria_Value=_value)) end=last;    if h.find() ne 0 then do; Criteria_Value=_value; h.replace();end;    else if findw(Criteria_Value, compress(_value)) = 0 then do; Criteria_Value=catx('-',Criteria_Value,_value);h.replace();end;    if last then h.output(dataset:'have2'); run; /*Step2: classical style transpose approach, nothing fancy*/ proc transpose data=have2 out=want prefix=C;   by ClientId notsorted;   var Criteria_Value;   id CriteriaId; run; Please note that I have completely rearranged your raw data, hopefully both of your concerns can be addressed. The only thing that could stop you from using Hash() is your table size bigger than the available RAM.  ... View more

I'm going to use the word "easy" on this one, but SAS has the ability to create ARM records, which can then be analyzed for a number of performance metrics, including CPU time. Here's a link to some documentation: http://support.sas.com/rnd/scalability/tools/arm/arm.html Tom ... View more

This does the trick : data mylib.mytest; SET mylib.'mytable#022'n ;run; Thank you! ... View more

Thank you very much for the quick reply. I am using SAS enterprise 4.1 and I use the built-in function to import file to SAS server. Do you know anyway to get the infile# of the file imported? In the previous post, the file # was #LN00013. Thank you for your help and time. ... View more

Thank you very much for the recommandation. Best regards. ... View more

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