3

I have a SAS macro below that is not working--- this snippet returns no values because the where statement doesn't work. Anyone have any ideas? I tried adding %str but that didn't work either.

%macro refreshments(beverage_type=);

proc sql;
select

*

where drink_type = '&beverage_type.'
;
quit;

%mend

%refreshments(Sprite);

Thanks.

CaptainBear
  • 167
  • 3
  • 12
  • 2
    Your SQL is missing a 'from' clause. You need to specify the name of the table you want to query. – user667489 Jul 22 '14 at 15:43
  • 1
    In future, if you have difficulties getting a macro working, try removing the macro code and running it as regular SAS code. Once that is working, add the macro code back in. – Robert Penridge Jul 22 '14 at 15:50

1 Answers1

4

Macro variables will not resolve in single quotes. You are also missing the FROM clause, and the macro parameter was being provided as positional (instead of name=value pair). Try the following:

%macro refreshments(beverage_type=);
  proc sql;
  select * 
    from YOURTABLE
    where drink_type = "&beverage_type";
%mend;

%refreshments(beverage_type=Sprite);
Allan Bowe
  • 12,306
  • 19
  • 75
  • 124
  • You also changed the single quotes to double quotes which will make a diff. – Robert Penridge Jul 22 '14 at 15:50
  • 1
    Note, this won't work if you're using pass-through to some DBMSs (Which won't allow double quotes in this fashion as they use them to enclose column names). See http://stackoverflow.com/questions/18323619/single-quoting-values-using-sql-to-create-macro-variable?rq=1 for example. – Joe Jul 22 '14 at 16:03