The main problem is your macro is generating this condition in the WHERE clause   state=&state If you pass in a value of ('CA','NY') then your macro will generate this syntax state=('CA','NY') Which is invalid. Change your code to use the IN operator and your call should work. state in (&state) Note that the extra () in your macro call is needed because you have a comma in the value.  The extra () do not cause a problem for the IN operator.   But you could also just use a space instead of a comma between the values and then you would not need the () in the macro call.  The IN operator is just as happy to have a space delimited list as a comma delimited list. %profile (today= %sysfunc(today(),date9.), state='CA' 'NY', age=40, year=2016);   ... View more

i made a small change to the code and it worked.   i've put 'and' instead of 'or'   %macro freq(year=,type=); proc sql; select product_id,total_sales,order_type,year from( select product_id,year(order_date) as year ,order_type ,sum(quantity) as total_sales from sales_data group by 1,2,3)%if %length(&type)>0 %then %do; where year=&year and order_type=&type;%end;%else %do;where year=&year;%end; quit; %mend; %freq(year=2007);   ... View more