Hi all,
I have the following code that update the strings stored in a column,
it is working correctly however i'd like to perfor better.
proc import
datafile="/Path/EventosFrases.xlsx"
out=FrasesAux
dbms=xlsx
replace;
sheet="Frases";
getnames=yes;
run;
data remov;
set FrasesAux;
frase=tranwrd(frase,"'","");
run;
%global str_update_
dt_max
num
num_id
id
idevento
update
i;
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;
%macro seleciona();
proc sql;
select count(distinct(idevento)) into :idevento from remov;
select distinct(idevento) into :idevento_dis_1-:idevento_dis_%cmpres(&idevento.) from remov;
select frase into :str_update_1-:str_update_%cmpres(&idevento.)
from remov;
quit;
data full(rename=(frase2=frase));
length frase2 $ 160;
set test;
%do i = 1 %to &idevento.;
%let update1=str_update_&&i.;
%let id1 = idevento_dis_&&i.;
if idevento = &&&id1. then frase2 = tranwrd("&&&update1","<var1>","Cli_Name");
output;
%put &&&update1. &&&id1.;
%end;
drop frase;
run;
proc sql;
select distinct * from full
where frase is not missing;
drop table full;
quit;
%mend;
%seleciona();
Where the part of %do loop runs, it create or duplicates a bunch of lines to the full table.
I'd like to stop this, because this is only a test, but this query will run with larger tables, if this duplicates keep happening this could exceed the Memory size.
How can i solve this ?
Thanks in Advance
Hi
The output statement in your macro will write a record for every iteration of the macro loop but I assume that you only want to write a record if idevento is matched.
If you wrap your creation of frase2 and the output statement into a if...then...do...end (as below) it will only write the cases you want.
if idevento = &&&id1. then do;
frase2 = tranwrd("&&&update1","<var1>","Cli_Name");
output;
end;
Cheers
Chris
Hi
The output statement in your macro will write a record for every iteration of the macro loop but I assume that you only want to write a record if idevento is matched.
If you wrap your creation of frase2 and the output statement into a if...then...do...end (as below) it will only write the cases you want.
if idevento = &&&id1. then do;
frase2 = tranwrd("&&&update1","<var1>","Cli_Name");
output;
end;
Cheers
Chris
But how would it be in a proc sql ?
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
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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: