Hi You need to provide examples of the input data, i.e. what you have. Without knowing what dt3 looks like no one will be able to help. Cheers Chris ... View more

Hi All macro variables are character, there are no numeric variables. If you want to perform a calculation use the %eval macro function, for example %eval(0.636805089257504*(&A.=1)+0.656423671305948*(&A.=2)); This should give you the expected results Cheers Chris ... View more

Hi   I had some ideas about your question regarding doing this in PROC SQL and came up with a solution that doesn't need a macro.   I made an assumption that you only want the latest values for id frase from the Excel import to generate the message with the client name. If this is wrong you can amend the SQL code below to create the data you need as it is quite simple, for example adding in a date from the "test" data to use in the join, or creating a cartesian product by joining all records with the same idevento from each dataset.   There are two SQL steps shown - the first one is commented out but could be used if you do not want any message about remerging statistics; the scond one is simpler but will generate a message about remerging statistics.   Let me know if you have any questions!   Hope this helps.   Cheers   Chris aka Rivieralad   proc import         datafile="/Path/EventosFrases.xlsx"         out=FrasesAux         dbms=xlsx         replace;         sheet="Frases";         getnames=yes; run;    data remov;    set FrasesAux;    frase=tranwrd(frase,"'",""); run; data test;    length idevento 3 frase $ 160;    input idevento frase;    cards; 2 fasffsdhfh 2 fshsfdh 3 frhdsfhgfjk 5 erwyfdhgfj 7 hfdh 7 hgdjtydkul 8 thutruili 8 utrhfghgfh ; run; proc sql; /*    create table full as     select         c.idevento         ,tranwrd(d.frase,"<var1>","Cli_Name") as frase /* as per your original macro */         ,tranwrd(d.frase,"<var1>",trim(c.frase)) as frase2 /* example of how to change <var> to text in dataset test */     from         test c     left join (         select             idevento             ,iddate             ,frase         from             remov a         where iddate ge (             select                 max(iddate)             from                 remov b             where                 a.idevento eq b.idevento             group by                 idevento             )         ) as d         on c.idevento eq d.idevento     ; */     create table full as     select         c.idevento         ,tranwrd(d.frase,"<var1>","Cli_Name") as frase         ,tranwrd(d.frase,"<var1>",trim(c.frase)) as frase2     from         test c     left join (         select             idevento             ,iddate             ,frase         from             remov         group by             idevento         having             iddate eq max(iddate)         ) d         on c.idevento eq d.idevento     ; quit; ... View more

Hi   What are you expecting in the way of output?   I think you have a bug in your macro variables. Looking at your original code, you write each distinct idevento into a macro variable but you then write each frase in turn into a macro variable. In your data you have six unique values for idevento which are written to nacro variables but you then write frase from the first six records to macro variables, so you write idevento and frase from record 1, idevento from record 3 and frase from record 2, idevento from record 4 and frase from record 3...and so on (this will then match data incorrectly with the dataset test). I reran your code and printed the macro variables to the log so you can see what I mean - lines are in red below.   To run this in SQL you would need to fix this first, and then decide how to treat records with duplicate idevento values.   Cheers   Chris 513 514  %macro seleciona(); 515 516  proc sql; 517     select count(distinct(idevento)) into :idevento from remov; 518 519     select distinct(idevento) into :idevento_dis_1-:idevento_dis_%cmpres(&idevento.) from 519! remov; 520 521          select frase into :str_update_1-:str_update_%cmpres(&idevento.) 522          from remov; 523  quit; 524  %put >>>>>; 525  %put &idevento; 526  %do i = 1 %to &idevento; 527      %put &&idevento_dis_&i   &&str_update_&i; 528  %end; 529  %put <<<<<; 530  data full/*(rename=(frase2=frase))*/; 531     length frase2 $ 160; 532     set test; 533  %do i = 1 %to &idevento.; 534  %let update1=str_update_&&i.; 535  %let id1 = idevento_dis_&&i.; 536 537     if idevento = &&&id1. then do; 538          frase2 = tranwrd("&&&update1","<var1>","Cli_Name"); 539          output; 540      end; 541 542  %put &&&update1. &&&id1.; 543  %end; 544  *drop frase; 545  run; 546 547  proc sql; 548          select distinct * from full 549          where frase is not missing; 550          *drop table full; 551  quit; 552 553  %mend; 554 555  %seleciona(); NOTE: PROCEDURE SQL used (Total process time):       real time           0.11 seconds       cpu time            0.01 seconds >>>>> 6 2   Ola <var1>, confirmamos a troca do seu pacote Empresa atual. Divirta-se! 3   Oi <var1>, a Empresa tem uma boa noticia pra te dar! Seu pacote foi alterado com sucesso. Obrigado por nos escolher e divirta-se! 4   Ola cliente, a programacao do canal opcional estara disponivel na sua Empresa em 24 horas. Divirta-se! 5   Ola <var1>, teste <var3>. 7   Ola <var1>, recebemos o pedido de cancelamento da sua Empresa. O sinal sera desligado em breve. Ligue 10611 para continuar c/ a assinatura. 8   Ola <var1>, a data de vencimento da sua fatura Empresa foi alterada. Obrigado. <<<<< Ola <var1>, confirmamos a troca do seu pacote Empresa atual. Divirta-se! 2 Oi <var1>, a Empresa tem uma boa noticia pra te dar! Seu pacote foi alterado com sucesso. Obrigado por nos escolher e divirta-se! 3 Ola cliente, a programacao do canal opcional estara disponivel na sua Empresa em 24 horas. Divirta-se! 4 Ola <var1>, teste <var3>. 5 Ola <var1>, recebemos o pedido de cancelamento da sua Empresa. O sinal sera desligado em breve. Ligue 10611 para continuar c/ a assinatura. 7 Ola <var1>, a data de vencimento da sua fatura Empresa foi alterada. Obrigado. 8 NOTE: There were 8 observations read from the data set WORK.TEST. NOTE: The data set WORK.FULL has 8 observations and 3 variables. NOTE: DATA statement used (Total process time):       real time           0.01 seconds       cpu time            0.01 seconds NOTE: PROCEDURE SQL used (Total process time):       real time           0.07 seconds       cpu time            0.01 seconds ... View more