Macro error Apparent symbolic reference

[meckarthik]

Getting macro error  (Need Help!!!)

Basically I tried to run  below

%macro assign;

  proc sql noprint;

    select count(distinct(Identifier)) into :z

    from &Olive_Schema..owner_metadata ;

  quit;

  %let z = &z;

%do j = 1 %to &z;

%LET Identify = &j;

Data test_& Identify;

Set test_&Identify;

Run;

It was running fine until here, then when  introduced

proc sql noprint;

    select count(distinct(weeks)) into :r

    from iter1_&Identify ;

  quit;

  %let r = &r;

  proc sql noprint;

  select distinct weeks

    into :t1 - :t&r.

    from iter1_&Identify ;

  quit;

 %let r = &r;

%do i = 1 %to &r;

%LET ctr_wk = &&t&i;

Data test_&ctr_wk;

Set test;run;

%end;

%end;

%mend;

%assign;

WARNING: Apparent symbolic reference R not resolved.

ERROR: The text expression &R contains a recursive reference to the macro variable R.  The macro variable will be assigned the null

       value.

ERROR: %EVAL function has no expression to evaluate, or %IF statement has no condition.

ERROR: The %TO value of the %DO I loop is invalid.

ERROR: The macro ASSIGN will stop executing.

[Tom · Accepted solution]

In general it is probably a bad idea to take data out of your dataset and put it into a series of macro variables.  You should probably take a step back a re-think how you are doing this process.

From the code you posted the only way R is not defined is if the the first query returns no results.  The INTO clause of the PROC SQL SELECT statement does not create the macro variables when no observations are selected.

You can simplify your query and solve this problem also by taking advantage of the automatic macro variable SQLOBS.  There is no need to count the number of distinct weeks in advance. If you just add the bare hyphen after the macro variable name then SAS knows you want to create a series of macro variables with numeric suffixes.  If you have a really (really) old version of SAS then put a nominal upper bound, like :t1 -:t9999 , it will only create the macro variables it needs.

proc sql noprint;
select distinct weeks
  into :t1 - 
  from iter1_&Identify 
;
%let r=&sqlobs;
quit;

Now R always have a value, even if sometimes it is zero.

[meckarthik]

THank you so much!