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)
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 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.
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
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).
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!
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.
Ready to level-up your skills? Choose your own adventure.