How to scan a numeric variable

Question

I have a table like this:

 Lista_ID  1 4 7 10 ... 

in total there are 100 numbers.

I want to call each one of these numbers to a macro i created. I was trying to use 'scan' but read that it's just for character variables. the error when i runned the following code was

there's 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 error was that:

VARIABLE PERC IS UNITIALIZED and
VARIABLE FIRST.ID_CLIENTE IS UNITIALIZED.

What I want is to run this macro for each one of the Id's in the List I showed before, and that are referenced in work.base&ref and work.up&ref. How can I do it? What I'm doing wrong?

thanks!

Answer 1

Here's the CALL EXECUTE version.

%MACRO CICLO_teste(REF);

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

%CICLO_TESTE;

DATA _NULL_;
SET amostra;
*CREATE YOUR MACRO CALL;
STR = CATT('%CLIO_TESTE(', ID, ')');
CALL EXECUTE(STR);
RUN;

Answer 2

First you should note that SAS macro variable resolve is intrinsically a "text-based" copy-paste action. That is, all the user-defined macro variables are texts. Therefore, %eval is unnecessary in this case.

Other miscellaneous corrections include:

• Check the %scan() function for correct usage. The first argument should be a text string WITHOUT QUOTES.

• run is redundant in proc sql since each sql statement is run as soon as they are sent. Use quit; to exit proc sql.

• A semicolon is not required for macro call (causes unexpected problems sometimes).

• use %do %to for loops

The code below should work.

data work.amostra;
    input id;
    cards;
1 
4 
7 
10
;
run;

proc sql noprint; 
    select id into :lista_id separated by ' ' from work.amostra;
    select count(*) into :nr separated by ' ' from work.amostra;
quit;

* check;
%put lista_id=&lista_id nr=&nr;

%macro ciclo_teste();
    %local ref;
    %do i = 1 %to &nr;
        %let ref = %scan(&lista_id, &i); 
        %*check;
        %put ref = &ref;

        /* your task below */

/*        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()

tested on SAS 9.4 win7 x64

Edited:

In fact I would recommend doing this to avoid scanning a long string which is inefficient.

%macro tester();
    /* get the number of obs (a more efficient way) */
    %local NN;
    proc sql noprint;
        select nobs into :NN
        from dictionary.tables
        where upcase(libname) = 'WORK' 
            and upcase(memname) = 'AMOSTRA';
    quit;

    /* assign &ref by random access */
    %do i = 1 %to &NN;
        data _null_;
            a = &i;
            set work.amostra point=a;
            call symputx('ref',id,'L');
            stop;
        run;
        %*check;
        %put ref = &ref;

        /* your task below */
    %end;
%mend;

%tester()

Please let me know if you have further questions.

Answer 3

Wow that seems like a lot of work. Why not just do the following:

data work.amostra;
input id;
cards;
1 
4 
7 
10
;
run;

%macro test001;
  proc sql noprint;
       select count(*) into: cnt
              from amostra;
  quit;
  %let cnt = &cnt;

  proc sql noprint;
       select id into: x1 - :x&cnt
              from amostra;
  quit;

  %do i = 1 %to &cnt;
    %let x&i = &&x&i;
    %put &&x&i;
  %end;

%mend test001;
%test001;

now in variables &x1 - &&x&cnt you have your values and you can process them however you like.

Answer 4

In general if your list is small enough (macro variables are limited to 64K characters) then you are better off passing the list in a single delimited macro variable instead of multiple macro variables.Remember that PROC SQL will automatically set the count into the macro variable SQLOBS so there is no need to run the query twice. Or you can use %sysfunc(countw()) to count the number of entries in your delimited list.

proc sql noprint ;
  select id into :idlist separated by '|' from .... ; 
%let nr=&sqlobs;
quit;
...
%do i=1 %to &nr ;
  %let id=%scan(&idlist,&i,|);
  data up&id ;
  ...
%end;

If you do generate multiple macro variables there is no need to set the upper bound in advance as SAS will only create the number of macro variables it needs based on the number of observations returned by the query.

select id into :idval1 - from ... ;
%let nr=&sqlobs;

If you are using an older version of SAS the you need set an upper bound on the macro variable range.

select id into :idval1 - :idval99999 from ... ;