topic Re: Please check how to correct the error message for this query m105p01.sas in SAS Programming

Please check how to correct the error message for this query m105p01.sas

hmlong25 — Thu, 13 Mar 2025 21:17:48 GMT

m105p01.sas

%Macro GroupList(Tab, Col);
title "Group: &&Val&i";
Proc SQL noprint;
Select Distinct Upcase(&Col)
into :Val1-
From
quit;

Data
%do i=1 %to &sqlobs;
Title "Group: &&Val&i ";

proc print data=&Tab (obs=10);
where upcase(&Col)="&&Val&i";
%end;
run;
%mend GroupList;
Options Mprint MLogic;

%GroupList(sashelp.cars, DriveTrain)

Re: Please check how to correct the error message for this query m105p01.sas

Tom — Thu, 13 Mar 2025 23:06:41 GMT

You need to start with working SAS code before trying to use a macro to generate SAS code.

Your posted code is not valid.

title "Group: &&Val&i";
Proc SQL noprint;
Select Distinct Upcase(&Col)
into :Val1-
From
quit;

Data

Run it without the macro wrapper to get a better set of error messages.

WARNING: Apparent symbolic reference I not resolved.
1    title "Group: &&Val&i";
WARNING: Apparent symbolic reference VAL not resolved.
WARNING: Apparent symbolic reference I not resolved.
2    Proc SQL noprint;
3    Select Distinct Upcase(DriveTrain)
4    into :Val1-
5    From
6    quit;
ERROR: File WORK.QUIT.DATA does not exist.
7
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds



8    Data
9
10   proc print data=sashelp.cars(obs=10);
                    -
                    22
                    76
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, (, /, ;, _DATA_, _LAST_, _NULL_.

ERROR 76-322: Syntax error, statement will be ignored.

11   run;

Re: Please check how to correct the error message for this query m105p01.sas

Ksharp — Fri, 14 Mar 2025 01:47:32 GMT

Get rid of NOPRINT option with PROC SQL.
System macro variable &sqlobs. will always get 0 if you specify NOPRINT.

Proc SQL noprint;
---->
Proc SQL ;

And if you do not want print these value. Try to create a NULL dataset:

Proc SQL ;
create table _null_ as
Select Distinct Upcase(&Col)
into :Val1-
From
quit;

Re: Please check how to correct the error message for this query m105p01.sas

Tom — Fri, 14 Mar 2025 21:26:02 GMT

@Ksharp Your answer is completely wrong here.

The SQLOBS macro variable will be set properly when NOPRINT option is used. Here is a simple example:

1    proc sql noprint;
2    select name into :males separated by ' ' from sashelp.class where sex='F';
3    %put &=sqlobs;
SQLOBS=9
4    select name into :unknown separated by ' ' from sashelp.class where sex not in ('M' 'F');
NOTE: No rows were selected.
5    %put &=sqlobs;
SQLOBS=0
6    quit;

The problem with the code is it is not actually querying from any table, so it tries to query a table named QUIT.

Re: Please check how to correct the error message for this query m105p01.sas

Ksharp — Sat, 15 Mar 2025 06:05:22 GMT

Tom,

Here is what I got.

1    proc sql noprint;
2    select name from sashelp.class;
3    quit;
NOTE: “PROCEDURE SQL”所用时间（总处理时间）:
      实际时间          0.10 秒
      CPU 时间          0.03 秒


4    %put &sqlobs. ;
1

1    proc sql ;
2    select name from sashelp.class;
NOTE: 正在写入 HTML Body（主体）文件: sashtml.htm
3    quit;
NOTE: “PROCEDURE SQL”所用时间（总处理时间）:
      实际时间          0.84 秒
      CPU 时间          0.28 秒


4    %put &sqlobs. ;
19

Re: Please check how to correct the error message for this query m105p01.sas

Tom — Sat, 15 Mar 2025 16:32:21 GMT

Yes. If you tell SQL to not produce ANY output then it doesn't actually bother to run the query.

But the query in question did produce output, the values put into macro variable(s).

Re: Please check how to correct the error message for this query m105p01.sas

Ksharp — Sun, 16 Mar 2025 01:04:09 GMT

Maybe OP has an old version sas.
When I do the same thing with very old sas, I am really running into the same problem.