topic Re: Macro Loop in SAS Programming

Macro Loop

DartRodrigo — Fri, 03 Jun 2016 13:47:08 GMT

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

Re: Macro Loop

rivieralad — Fri, 03 Jun 2016 14:58:17 GMT

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

Re: Macro Loop

DartRodrigo — Fri, 03 Jun 2016 16:18:27 GMT

But how would it be in a proc sql ?

Re: Macro Loop

rivieralad — Mon, 06 Jun 2016 08:56:26 GMT

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

Re: Macro Loop

rivieralad — Mon, 06 Jun 2016 12:39:56 GMT

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;