BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
hmlong25
Obsidian | Level 7

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)

hmlong25_0-1741900525112.pnghmlong25_1-1741900600389.png

 

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
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;

View solution in original post

6 REPLIES 6
Tom
Super User Tom
Super User

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;
Ksharp
Super User
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;
Tom
Super User Tom
Super User

@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.

 

Ksharp
Super User

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

Tom
Super User Tom
Super User

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).

Ksharp
Super User
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.

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 6 replies
  • 998 views
  • 1 like
  • 3 in conversation