Hi guys. I have a bug problem I cannot fix, this is my code. productname is a macro variable "a b c d" and sqlobs is 4 in my case. My logic is using macro do loop and %scan function to scan the product name each time and output the data using proc report step. But when I run the data, nothing comes out. I don't know why?
data order;
input ordernumber$ product$ qrt;
cards;
##1 a 1000
##2 b 200
##3 c 3000
##4 d 200
;
run;
proc sql;
select distinct product into: productname separated by ' '
from order;
run;
%macro report;
%do i=1 %to &sqlobs;%macro a; %mend a;
%Let name=%scan(productname,&i,' ');
proc print data=order;
var ordernumber qrt;
where Product="&name";
title '&name';
run;
%end;
%mend;
%report;
First thing is you are not %scanning the macro variable. When you use this code
%Let name=%scan(productname,&i,' ');
You scan the literal text. To use the macro variable:
%Let name=%scan(&productname,&i,' ');
I have no idea what you intend with the
%macro a; %mend a;
but it is likely a poor idea. Recompiling macros inside another macro is one road to macro madness.
Your title will not render as desired because you have the macro variable inside single quotes in
title '&name';
Better practice would be to either place the Proc SQL inside the macro OR to make the Productname macro variable a parameter passed to the macro. Having macro variables appear in code without an obvious source can be difficult to debug later. And you don't actually need the SQLObs variable.
%do i=1 %to %sysfunc(countw(&productname));
will count the number of space delimited words in &productname and use that as the limit.
Are there errors in the log?
Please put this command at the start of your program and then run it gain
options mprint symbolgen mlogic;
Then post the ENTIRE log, by clicking on the </> icon and paste the log as text into the window that appears. This maintains the formatting of the log and makes it much easier to read and understand . DO NOT SKIP THIS STEP.
First thing is you are not %scanning the macro variable. When you use this code
%Let name=%scan(productname,&i,' ');
You scan the literal text. To use the macro variable:
%Let name=%scan(&productname,&i,' ');
I have no idea what you intend with the
%macro a; %mend a;
but it is likely a poor idea. Recompiling macros inside another macro is one road to macro madness.
Your title will not render as desired because you have the macro variable inside single quotes in
title '&name';
Better practice would be to either place the Proc SQL inside the macro OR to make the Productname macro variable a parameter passed to the macro. Having macro variables appear in code without an obvious source can be difficult to debug later. And you don't actually need the SQLObs variable.
%do i=1 %to %sysfunc(countw(&productname));
will count the number of space delimited words in &productname and use that as the limit.
Hi ballardw, thank you so much for the quick reply.
Adding %macro a; %mend a; will let the SAS highlight the function for you. Easy to read. But thank you so much for the alternative way. Learn form this!
You don't need any macro coding for this. Sort by product, and use proc report or proc print with
by product;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.