topic Re: MAcro erros in New SAS User

MAcro erros

meckarthik — Sat, 25 May 2019 01:43:13 GMT

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)

Re: MAcro erros

SASKiwi — Sat, 25 May 2019 02:19:26 GMT

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;

Re: MAcro erros

Patrick — Sat, 25 May 2019 02:16:24 GMT

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

Re: MAcro erros

Astounding — Sat, 25 May 2019 05:46:33 GMT

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;

Re: MAcro erros

Tom — Sat, 25 May 2019 17:31:32 GMT

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;

Re: MAcro erros

meckarthik — Sat, 25 May 2019 20:23:48 GMT

it works, thank you!

Re: MAcro erros

meckarthik — Sat, 25 May 2019 20:24:54 GMT

it works, thank you for comprehensive guidance

Re: MAcro erros

meckarthik — Sat, 25 May 2019 20:25:20 GMT

Thank you!!