How to use scan for a numeric variable [SAS]

Question

I have a table like this:

Lista_id 1 4 7 10

(In total are 100 numbers)

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

Here follows 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 LISTA_ID and referenced in work.base&ref and work.up&ref. How can I do it?

Answer 1

Hello! First, I believe you are confusing the function scan (which only receives text values) with the macro function %scan. Whenever you have the symbol "%" before the function name, it is a macro function, which takes as argument a macro variable. Thus, the macro function %scan can also be used with numeric values.

I believe the Warning that showed up was something like "WARNING: Macro variable LIM_MSISDN is not initialized". This happened because you forgot to put ampersand (symbol &") before the NR text, indicating that NR is a macro variable, in this section here: %LET LIM_MSISDN = %EVAL(NR) . Also, you don’t need to use %val here, you only use %val when you will do some arithmetic operation with the macro variable value, which is not the case here; here you just want to copy the value. I put below two suggestions of codes that should work for what you want to do:

Code 1: I’m not sure if it works because I’ve never used '*' to separate elements into 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 the 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;