Hi There,
Need help on Macro error
i got min and max week number as below.. trying to create table based on week number between &min_weeks and &max_weeks but macro showing error with dot (.) instead of actual number
Proc sql ;
select min( week_number) into : MIN_WEEKS
from work.Data
quit;
%let MIN_WEEKS = %qtrim(&MIN_WEEKS);
%put &MIN_WEEKS ;
Proc sql ;
select max(week_number) into : MAX_WEEKS
from work.Data;
quit;
%let MAX_WEEKS = %qtrim(&MAX_WEEKS);
%put &MAX_WEEKS ;
PROC SQL;
SELECT distinct adj_week_number as weeks
into work.iter1
from work.Data
WHERE week_number BETWEEN &MIN_WEEKS. AND &MAX_WEEKS.;
quit;
But the macro &MIN_WEEKS., &MAX_WEEKS. not working , getting error
“WHERE week_number BETWEEN . AND . “ not sure why getting dot instead of number (e.g. 1 – min and 5- max)
A single period is how SAS represents missing numeric values. So if your variable is all missing then both the minimum and the maximum value will be represented by a period. What do you want to do in that case?
Also you can simplify how you make the macro variables so that they are not created with leading/trailing spaces.
proc sql noprint;
select min( week_number)
, max(week_number)
into :MIN_WEEKS trimmed
, :MAX_WEEKS trimmed
from work.Data
;
quit;
Try removing the space between the colon and the macro variable. If that doesn't help post your SAS log.
Proc sql ;
select min( week_number) into :MIN_WEEKS
from work.Data;
quit;
Thank you!!
There are syntax errors in the code you've posted so that can't be the version you've executed and then had problems with.
1. Missing semicolon in the first SQL
2. "into work.iter1" is not valid SAS SQL syntax the way you use it
There is also a logical issue with your code.
You are selecting the minimum and maximum week_number from the whole table and populate these values into SAS macro variables. You then use these macro variable in a between condition on the exactly same table using the exactly same variable from where you've derived these values. So... this condition will always be true and though is rather useless.
Below your code with the syntax errors fixed and posted using the running man icon to not mess-up the code.
data data;
do week_number=1 to 5;
adj_week_number+5;
output;
end;
stop;
run;
Proc sql noprint;
select min( week_number) into : MIN_WEEKS
from work.Data
;
quit;
%let MIN_WEEKS = %qtrim(&MIN_WEEKS);
%put &MIN_WEEKS;
Proc sql noprint;
select max(week_number) into : MAX_WEEKS
from work.Data
;
quit;
%let MAX_WEEKS = %qtrim(&MAX_WEEKS);
%put &MAX_WEEKS;
PROC SQL;
SELECT distinct adj_week_number as weeks
from work.Data
WHERE week_number BETWEEN &MIN_WEEKS. AND &MAX_WEEKS.;
quit;
And as a side note here how you can populate your min & max macro variables in a single SQL and with left aligned values.
proc sql noprint;
select
put(min(week_number),32. -l),
put(max(week_number),32. -l)
into
:min_weeks,
:max_week
from work.data
;
quit;
%put &=min_weeks;
%put &=max_weeks;
it works, thank you for comprehensive guidance
A single period is how SAS represents missing numeric values. So if your variable is all missing then both the minimum and the maximum value will be represented by a period. What do you want to do in that case?
Also you can simplify how you make the macro variables so that they are not created with leading/trailing spaces.
proc sql noprint;
select min( week_number)
, max(week_number)
into :MIN_WEEKS trimmed
, :MAX_WEEKS trimmed
from work.Data
;
quit;
it works, thank you!
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!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.