Macro Loop

[DartRodrigo]

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

[rivieralad · Accepted solution]

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

[DartRodrigo]

But how would it be in a proc sql ?

[rivieralad]

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

[rivieralad]

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;