macro – How to use 'scan' for a numeric variable [SAS]

Question:

I have a table like this:

List_ID 1 4 7 10

(In total there are 100 numbers)

I want to call them to a macro I created. I was trying to do it with Scan, but I read it was just for text variables. It gave me that my variables were not initialized.

Here is the code:

proc sql; 
select ID INTO: LISTA_ID SEPARATED BY '*' from 
WORK.AMOSTRA;
run;


PROC SQL;
SELECT COUNT(*) INTO: NR SEPARATED BY '*' FROM
WORK.AMOSTRA;
RUN;

%MACRO CICLO_teste();

%LET LIM_MSISDN = %EVAL(NR);
%LET I = %EVAL(1);

%DO %WHILE (&I<= &LIM_MSISDN);
%LET REF = %SCAN(LISTA_ID,&I,,'*'); 

DATA WORK.UP&REF;
SET WORK.BASE&REF;
FORMAT PERC_ACUM 9.3;
IF FIRST.ID_CLIENTE THEN PERC_ACUM=0;
PERC_ACUM+PERC;
RUN; 



%LET I = %EVAL(&I+1);
%END;
%MEND;

%CICLO_TESTE;

The idea is to run this macro for each of the ID's contained in LIST_ID and referenced in work.base&ref and work.up&ref. How can I do it?

Answer:

Hi! First of all, I believe you are confusing the scan function (which only takes text values) with the %scan macro function. Whenever you have the symbol " % " before the function name, it is a macro function, which takes a variable macro as argument. In this way, the %scan macro function can also be used with numerical values.

I believe the warning that appeared was something like "WARNING: Macro variable LIM_MSISDN is not initialized" . This is because you forgot to put the ampersand (& symbol) before the NR text, indicating that NR is a macro variable, in this snippet here: %LET LIM_MSISDN = %EVAL(NR) . Also, you don't need to use %eval here, you only use %eval when you're going to do some arithmetic operation with the macro variable value, which is not the case here; here you just want to copy the value. I've put down two code suggestions that should work for what you want do:

Code 1: I'm not sure if it works because I've never used '*' to separate elements in macro variables and I don't have SAS here to test:

proc sql; 
    select ID INTO: LISTA_ID SEPARATED BY '*' from 
    WORK.AMOSTRA;
quit;

%MACRO CICLO_teste;

    %let num_elementos = %sysfunc(countw(&LISTA_ID.), %str(*));

    %do i = 1 %to &num_elementos.;

        %let ref = %scan(&LISTA_ID., &i., *);

        DATA WORK.UP&REF;
            SET WORK.BASE&REF;
            FORMAT PERC_ACUM 9.3;
            IF FIRST.ID_CLIENTE THEN PERC_ACUM=0;
        PERC_ACUM+PERC;
    RUN; 
    %end;

%MEND CICLO_TESTE;
%CICLO_TESTE;

Code 2: using space to separate macro variable elements:

proc sql; 
    select ID INTO: LISTA_ID SEPARATED BY ' ' from 
    WORK.AMOSTRA;
quit;

%MACRO CICLO_teste;

    %let num_elementos = %sysfunc(countw(&LISTA_ID.));

    %do i = 1 %to &num_elementos.;

        %let ref = %scan(&LISTA_ID., &i.,' ');/*Como usei espaço como separador, nem precisava colocar o terceiro argumento, mas deixei aqui pra ficar explícito*/

        DATA WORK.UP&REF;
            SET WORK.BASE&REF;
            FORMAT PERC_ACUM 9.3;
            IF FIRST.ID_CLIENTE THEN PERC_ACUM=0;
        PERC_ACUM+PERC;
    RUN; 
    %end;

%MEND CICLO_TESTE;
%CICLO_TESTE;
Scroll to Top