How to pass macro variable in Proc SQL functions

[vishalrajpoot3]

Hello SAS Community,

I am facing problem while I am passing the macro variable in mean or any other aggregation function in SAS SQL. It is providing mean of first variable only but I want mean of all the variable and group by with names. Below is the code for your reference:

data new; set sashelp.class;
if name = 'Alfred' then name = 'Alice';
If name = 'Janet' then name = 'Judy';
if name = 'Robert' then name = 'Ronald';
run;

data a;
set sashelp.class(keep=Height Weight);
run;

proc contents data=a out=b(keep=name);
run;

proc sql;
select Name into: Name_4 separated by ','  from b;
quit

proc sql;
create table new_1 as select Name, mean(&Name_4.) as M_&Name_4.
from new group by name;
run;

A quick help is much appreciated. 

[PeterClemmensen]

I don't understand this. What is your desired result given this sample data? Please show us.

[Kurt_Bremser]

If you want to calculate means (or other statistics) for multiple variables, PROC SUMMARY is the tool of choice.

If you have a macro variable containing your variable names (separated by blanks), it's just

proc summary data=new nway;
class name;
var &names.;
output out=want mean()=;
run;

[vishalrajpoot3]

There are multiple variables I have and multiple aggregation needed. After that I need to group by on names.

[Quentin]

Your current code results in a macro variable &Name_4 with the value: Height,Weight.  

When you code:

proc sql;
create table new_1 as select Name, mean(&Name_4.) as M_&Name_4.
from new group by name;
quit ;

After the macro substitution your code is:

proc sql;
create table new_1 as select Name, mean(Height,Weight) as M_Height,Weight
from new group by name;
quit ;

That code will not calculate the mean of Height.  It calculates the mean of height and weight, for each record.  Note you should have a warning in your log, because the the expression does not have a summary function:

WARNING: A GROUP BY clause has been transformed into an ORDER BY clause because neither the SELECT
         clause nor the optional HAVING clause of the associated table-expression referenced a
         summary function.

 If you would approach this with the macro language, you will need a macro to generate SQL code like:

proc sql;
create table new_1 as select Name
, mean(Height) as M_Height
, mean(Weight) as M_Weight
from new group by name;
quit ;

This is a straight forward macro exercise, because the macro language is good at processing lists.  So you can take a list of variable names, and use it to generate a list of SQL statements.  But as Kurt mentioned, PROC SUMMARY will cost you less code to write.

[Tom · Accepted solution]

Note that PROC SQL is NOT the right tool for generating the mean of multiple variables.  Use PROC MEANS (aka PROC SUMMARY).

But to fix your current code you need to do two things.

1) Figure out what SQL code you need to generate.

2) Change your macro variable to contain that code.

data new ;
  set sashelp.class;
  if name = 'Alfred' then name = 'Alice';
  if name = 'Janet' then name = 'Judy';
  if name = 'Robert' then name = 'Ronald';
run;

proc contents noprint data=new(keep=height weight) out=names(keep=name);
run;


proc sql noprint;
select catx(' ','mean(',Name,') as',cats('m_',name))
  into :sql_code separated by ','  
  from names
;
%put &=sql_code ;
create table new_1 as
  select Name, &sql_code
  from new 
  group by name
;
quit;