topic How to combine multiple select statements under one PROC SQL in Statistical Procedures

How to combine multiple select statements under one PROC SQL

mmhxc5 — Wed, 23 Sep 2020 20:15:08 GMT

Hi all,

Hope you all are doing great and stay safe during the pandemic. I would like to combine several "Select" statements where I calculate the average of the variables under one "Proc SQL". Below is the code I use and it works, but I use this code for each variable separately and producing unnecessary files. I want to get the average of other variables like length, age, ... for the same ID under this code. I appreciate your time and help.

Re: How to combine multiple select statements under one PROC SQL

Kurt_Bremser — Wed, 23 Sep 2020 20:34:22 GMT

proc sql;
create table want as
  select
    id,
    avg(var1) as var1,
    avg(var2) as var2
  from have
  group by id
;
quit;

Re: How to combine multiple select statements under one PROC SQL

Reeza — Wed, 23 Sep 2020 20:35:29 GMT

PROC MEANS is almost always a better option once you're looking at multiple variables, statistics and at different levels.
FYI - your SQL code was not included.