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