Macro variable not resolved

[sasecn]

I tried to used macro variables to create subsets from a large table. The example is to subset sashelp.cars into 3 tables by variable: Origin. The code was working in the past, at least worked in Nov. 2019. But now I got the error meassage:

WARNING: Apparent symbolic reference C not resolved.
ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was:
&c
ERROR: The %TO value of the %DO I loop is invalid.
ERROR: The macro T will stop executing.

I think it is because macro variable C was not resolved, but i could be wrong. Any help would be great, thanks!

proc sql;
create table test as
select distinct origin, count(distinct origin) into :origin1-, :c
from sashelp.cars
;
quit;

%macro t;
proc sql;
%do i=1 %to &c;
create table &&origin&i as
select origin
from sashelp.cars
where origin="&&origin&i";
%end;
quit;
%mend t;

%t

[Reeza]

Post the log and error. Make sure to have set the macro debugging options before running the code and then post the log with that extra information, if you don't figure it out from there.

options mprint symbolgen;

---

@sasecn wrote:

I tried to used macro variables to create subsets from a large table. The example is to subset sashelp.cars into 3 tables by variable: Origin. The code was working in the past, at least worked in Nov. 2019. But now I got the error meassage:

WARNING: Apparent symbolic reference C not resolved.
ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was:
&c
ERROR: The %TO value of the %DO I loop is invalid.
ERROR: The macro T will stop executing.

I think it is because macro variable C was not resolved, but i could be wrong. Any help would be great, thanks!

proc sql;
create table test as
select distinct origin, count(distinct origin) into :origin1-, :c
from sashelp.cars
;
quit;

%macro t;
proc sql;
%do i=1 %to &c;
create table &&origin&i as
select origin
from sashelp.cars
where origin="&&origin&i";
%end;
quit;
%mend t;

%t

---

[sasecn]

P.S. i am using EG 7

24 proc sql;
25 create table test as
26 select distinct origin, count(distinct origin) into :origin1-, :c
27 from sashelp.cars
28 ;
WARNING: INTO clause is ignored in the CREATE TABLE statement.
NOTE: The query requires remerging summary statistics back with the original data.
NOTE: Table WORK.TEST created, with 3 rows and 2 columns.

29 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.05 seconds
cpu time 0.04 seconds

30
31 %macro t;
32 proc sql;
33 %do i=1 %to &c;
34 create table &&origin&i as
35 select origin
36 from sashelp.cars
37 where origin="&&origin&i";
38 %end;
39 quit;
40 %mend t;
41
42 %t
WARNING: Apparent symbolic reference C not resolved.
ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was:
&c
ERROR: The %TO value of the %DO I loop is invalid.
ERROR: The macro T will stop executing.
43
2 The SAS System 13:43 Tuesday, February 18, 2020

44 GOPTIONS NOACCESSIBLE;
45 %LET _CLIENTTASKLABEL=;
46 %LET _CLIENTPROCESSFLOWNAME=;
47 %LET _CLIENTPROJECTPATH=;
48 %LET _CLIENTPROJECTNAME=;
49 %LET _SASPROGRAMFILE=;
50
51 ;*';*";*/;quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
cpu time 0.00 seconds

51 ! run;
52 ODS _ALL_ CLOSE;
53
54
55 QUIT; RUN;
56

[Reeza]

Well, the issue pretty much jumps out:

24 proc sql;
25 create table test as
26 select distinct origin, count(distinct origin) into rigin1-, :c
27 from sashelp.cars
28 ;
WARNING: INTO clause is ignored in the CREATE TABLE statement.
NOTE: The query requires remerging summary statistics back with the original data.
NOTE: Table WORK.TEST created, with 3 rows and 2 columns.

Why are you creating a table if you only need macro variables? Try removing the CREATE TABLE portion and see what happens. 

Spoiler

---

@sasecn wrote:

P.S. i am using EG 7

24 proc sql;
25 create table test as
26 select distinct origin, count(distinct origin) into :origin1-, :c
27 from sashelp.cars
28 ;
WARNING: INTO clause is ignored in the CREATE TABLE statement.
NOTE: The query requires remerging summary statistics back with the original data.
NOTE: Table WORK.TEST created, with 3 rows and 2 columns.

29 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.05 seconds
cpu time 0.04 seconds

30
31 %macro t;
32 proc sql;
33 %do i=1 %to &c;
34 create table &&origin&i as
35 select origin
36 from sashelp.cars
37 where origin="&&origin&i";
38 %end;
39 quit;
40 %mend t;
41
42 %t
WARNING: Apparent symbolic reference C not resolved.
ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was:
&c
ERROR: The %TO value of the %DO I loop is invalid.
ERROR: The macro T will stop executing.
43
2 The SAS System 13:43 Tuesday, February 18, 2020

44 GOPTIONS NOACCESSIBLE;
45 %LET _CLIENTTASKLABEL=;
46 %LET _CLIENTPROCESSFLOWNAME=;
47 %LET _CLIENTPROJECTPATH=;
48 %LET _CLIENTPROJECTNAME=;
49 %LET _SASPROGRAMFILE=;
50
51 ;*';*";*/;quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
cpu time 0.00 seconds

51 ! run;
52 ODS _ALL_ CLOSE;
53
54
55 QUIT; RUN;
56

---

[sasecn]

Thanks for your reply! NOVINOSRIN also point this out. I think that is the problem.

[Reeza]

The log tells you this is the problem.

[novinosrin · Accepted solution]

HI @sasecn  You can't CREATE TABLE when using INTO clause

Try the corrected below

proc sql;
select distinct origin, count(distinct origin) into :origin1-,  :c
from sashelp.cars
;
quit;

%put &=c;

%macro t;
proc sql;
%do i=1 %to &c;
create table &&origin&i as
select origin
from sashelp.cars
where origin="&&origin&i";
%end;
quit;
%mend t;

%t

[sasecn]

Thanks, i don't know that i cannot create table when using into.

[Tom]

Remove the CREATE TABLE part.  Add the NOPRINT option to avoid cluttering your output with the list of values. No need to count, SAS will count for you into the SQLOBS macro variable.  You can save the value into another macro variable if you want.

proc sql noprint;
select distinct origin into :origin1-
from sashelp.cars
;
%let c=&sqlobs;
quit;

[sasecn]

Thanks, good to know!