Yes the entries in var1, var2 and var3 are all different. This is not the case in the var 4-10 but I don't care which ones will be dropped. Is it possible to do it in a proc sql?   Thanks ... View more

Basically I have to translate this code   proc sort data=my_db nodupkey; by var1 var2 var3 var4; run; in a proc sql, however the database has more then only 4 variables. How can I do it? ... View more

Can I do that into a proc sql? I need it to be done in proc sql?   Thank you ... View more

Thank you very much for your help. I have found a solution that seems to be working as   SELECT DISTINCT data_sinistro, identificativo_veicolo, ifc(compress(identificativo_veicolo_denuncian,"* ")<compress(identificativo_veicolo_contropar,"* "),compress(identificativo_veicolo_denuncian,"* "),compress(identificativo_veicolo_contropar,"* ")) as targa1, ifc(compress(identificativo_veicolo_denuncian,"* ")>compress(identificativo_veicolo_contropar,"* "),compress(identificativo_veicolo_denuncian,"* "),compress(identificativo_veicolo_contropar,"* ")) as targa2, CASE WHEN ruolo_veicolo_richiesto = 'D' THEN 'D' ELSE 'C' END AS ruolo_veicolo_richiesto, 'NO_CARD' AS tipo FROM Ssrc WHERE calculated targa1 NOT IN ('','ZZ999ZZ', 'ZZ999XX', 'ZZ99999', 'ZZ000ZZ', 'XXXXXX', 'AA00000', 'AA000AA', 'AA000BB', 'AA000XX', 'AA11111', 'AA000ZZ', 'AA111BB', 'XX000XX', 'ELIMINAT', 'XX999XX', 'XX111XX', 'XX123XX', 'MI111111', 'PLPENPLP', 'XX000YY', 'RMZ94732', 'AA111AA', 'AA123BB', 'BS400BZ', 'KASKO', 'RCDIV','VARIE','TRASP','PEDONE','MOTVEL','FURTOT','FURTOP','IGNOTO','INCEND','FURTOR','AGG.TO','ESTERA','FURTOL') AND calculated targa2 NOT IN ('','ZZ999ZZ', 'ZZ999XX', 'ZZ99999', 'ZZ000ZZ', 'XXXXXX', 'AA00000', 'AA000AA', 'AA000BB', 'AA000XX', 'AA11111', 'AA000ZZ', 'AA111BB', 'XX000XX', 'ELIMINAT', 'XX999XX', 'XX111XX', 'XX123XX', 'MI111111', 'PLPENPLP', 'XX000YY', 'RMZ94732', 'AA111AA', 'AA123BB', 'BS400BZ', 'KASKO', 'RCDIV','VARIE','TRASP','PEDONE','MOTVEL','FURTOT','FURTOP','IGNOTO','INCEND','FURTOR','AGG.TO','ESTERA','FURTOL') AND data_sinistro is not missing); Now one last step. In PostgreSQL I would run the following code:   CREATE TABLE prova AS SELECT DISTINCT ON (data_sinistro, identificativo_veicolo, targa1, targa2) data_sinistro, identificativo_veicolo, targa1, targa2, ruolo_veicolo_richiesto, tipo FROM (****) Where the **** represents the block of code previously written. This codes eliminates duplicates based on this three fields only  (data_sinistro, identificativo_veicolo, targa1, targa2) Keeping all the variables data_sinistro, identificativo_veicolo, targa1, targa2, ruolo_veicolo_richiesto, tipo    In the final table. Is there something similar in SAS?   Thank you very much indeed for your help ... View more

Thank you,   My final query looks like this one   proc sql; CREATE TABLE prova AS SELECT DISTINCT data_sinistro, identificativo_veicolo, min(input(compress(identificativo_veicolo_denuncian," ","kd"),best.),input(compress(identificativo_veicolo_contropar," ","kd"),best.)) as targa1, max(input(compress(identificativo_veicolo_denuncian," ","kd"),best.),input(compress(identificativo_veicolo_contropar," ","kd"),best.)) AS targa2, CASE WHEN ruolo_veicolo_richiesto = 'D' THEN 'D' ELSE 'C' END AS ruolo_veicolo_richiesto, 'NO_CARD' AS tipo FROM Ssrc WHERE calculated targa1 NOT IN ('','ZZ999ZZ', 'ZZ999XX', 'ZZ99999', 'ZZ000ZZ', 'XXXXXX', 'AA00000', 'AA000AA', 'AA000BB', 'AA000XX', 'AA11111', 'AA000ZZ', 'AA111BB', 'XX000XX', 'ELIMINAT', 'XX999XX', 'XX111XX', 'XX123XX', 'MI111111', 'PLPENPLP', 'XX000YY', 'RMZ94732', 'AA111AA', 'AA123BB', 'BS400BZ', 'KASKO', 'RCDIV','VARIE','TRASP','PEDONE','MOTVEL','FURTOT','FURTOP','IGNOTO','INCEND','FURTOR','AGG.TO','ESTERA','FURTOL') AND calculated targa2 NOT IN ('','ZZ999ZZ', 'ZZ999XX', 'ZZ99999', 'ZZ000ZZ', 'XXXXXX', 'AA00000', 'AA000AA', 'AA000BB', 'AA000XX', 'AA11111', 'AA000ZZ', 'AA111BB', 'XX000XX', 'ELIMINAT', 'XX999XX', 'XX111XX', 'XX123XX', 'MI111111', 'PLPENPLP', 'XX000YY', 'RMZ94732', 'AA111AA', 'AA123BB', 'BS400BZ', 'KASKO', 'RCDIV','VARIE','TRASP','PEDONE','MOTVEL','FURTOT','FURTOP','IGNOTO','INCEND','FURTOR','AGG.TO','ESTERA','FURTOL') AND data_sinistro is not missing ; quit; but, unfortunately it returns the error   ERROR: Expression using IN has components that are of different data types. ERROR: Expression using IN has components that are of different data types. What am I doing wrong? ... View more

Thank you very much, the thing is that for each row I need the min of the two variables identificativo_veicolo_contropar and identificativo_veicolo_denuncian. in PostgreSQL this could be done with the instruction LEAST(VAR1,VAR2)   How can I do this? Can I be achieved as? min(input(compress(identificativo_veicolo_denuncian," ","kd"),best.),input(compress(identificativo_veicolo_contropar," ","kd"),best.)) as targa1,     Thank you ... View more

It is in this from ' *AA123BB' and with the SAS expression compress(identificativo_veicolo_denuncian,'* ') it becomes 'AA123BB'. Same applies to identificativo_veicolo_contropar. I need the MIN and MAX function to store in two different variables the MIN and MAX (alphabetically) the two strings.   Thank you ... View more

I have tried the following  min(input(char_variable1,best.),input(char_variable2,best.)) as min_result However I have errors. The code I am trying to convert is the following LEAST(REGEXP_REPLACE(REGEXP_REPLACE(identificativo_veicolo_denuncian,'\*',''),' ',''),REGEXP_REPLACE(REGEXP_REPLACE(identificativo_veicolo_contropar,'\*',''),' ','') ) AS targa1, GREATEST(REGEXP_REPLACE(REGEXP_REPLACE(identificativo_veicolo_denuncian,'\*',''),' ',''),REGEXP_REPLACE(REGEXP_REPLACE(identificativo_veicolo_contropar,'\*',''),' ','') ) AS targa2, Any help would be greatly appreciated.   Thank you  ... View more

Thank you, I used compress for REGEXP_REPLACE, and I have tried MIN and MAX, however MIN and MAX require numeric inputs, what if I have character strings?   Thank you ... View more