MAcro erros

[meckarthik]

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)

[SASKiwi]

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;

[meckarthik]

Thank you!!

[Patrick]

@meckarthik 

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;

[meckarthik]

it works, thank you for comprehensive guidance 

[Astounding]

In addition to the many good points already mentioned, only bad things happen when SQL tries to parse quoted strings. So get rid of %QTRIM. Instead use:

%let min_weeks = &min_weeks;
%let max_weeks = &max_weeks;

[Tom · Accepted solution]

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;

[meckarthik]

it works, thank you!