sas: proc sql select into with more than one output

Question

I have following dataset

data height;
    input name $ var $ value;
    datalines;
John test1 175
Peter test1 180
Chris test1 140
John test2 178
Peter test2 182
Chris test2 148
;
run;

I would like to make mean value of 2 tests for each students

I able to make a new variable named mean_v_John by following

proc sql;
    select mean(value) into: mean_v_John
    from height
    where name = 'John';
quit;

%put &mean_v_John.;

With output:

2216  %put &mean_v_John.;
176.5

Problem: How can i auto detect how many students and create variable with &mean_v_NAME? In this example there will be 3 variables.

Answer 1

If you start of with calculating the mean for each student, you can use that table to assign the macro variables with the call symput routine. Like:

data height;
input name $ var $ value;
datalines;
John test1 175
Peter test1 180
Chris test1 140
John test2 178
Peter test2 182
Chris test2 148
;
run;

proc sql noprint;
create table work.mean_height as
select name, mean(value) as mean_height
from work.height
group by name;

data _null_;
   set work.mean_height;
   call symput("mean_v_"||name,mean_height);
run;

%put John mean: &mean_v_John.;
%put Peter mean: &mean_v_Peter.;
%put Chris mean: &mean_v_Chris.;

Answer 2

While it's not possible to do this directly in SAS exactly like you want (in large part because this isn't something that is idiomatic in SAS), you can do something relatively close in one step.

If you don't use the actual name, but instead use incremental numbers, you can get the same basic result in a macro variable pseudo-array. See the following:

proc sql;
  select age, mean(height) into :age1-:age8, :height1-:height8
  from sashelp.class
  group by age;
quit;

%put _global_;

Now you have two sort-of-vectors/arrays, one that stores the ages and one that stores the corresponding mean height. You could do the same with your names.

Macro variable pseudo-arrays are not a language construct, though, they're something sort of backed into by people who want to use them this way; as a result there are a lot of papers out there on using them with various different custom macro implementations (google "SAS Macro Arrays", then pick the implementation you like best).