topic Re: how to sum all numeric variables in sql in SAS Programming

how to sum all numeric variables in sql

thanikondharish — Sun, 27 Jan 2019 07:35:05 GMT

how to sum all numeric variables in sashelp.class write do loop in sql

Re: how to sum all numeric variables in sql

PeterClemmensen — Sun, 27 Jan 2019 09:23:13 GMT

Why do you want to limit yourself to SQL? There are other procedures better suited for the job. Check out Proc Means or Proc Summary

Re: how to sum all numeric variables in sql

VDD — Sun, 27 Jan 2019 14:04:38 GMT

SQL server is not the same as SAS.

Let SAS do the heavy work and then push the results to the SQL server.

Re: how to sum all numeric variables in sql

Cynthia_sas — Sun, 27 Jan 2019 18:58:17 GMT

Hi:

SAS Procedures like PROC MEANS and PROC TABULATE (and others) support the use of _NUMERIC_ and _CHARACTER_ to deal with all the numeric or all the character variables. I do not believe you can use this syntax shortcut in PROC SQL.

Using SAS procedures you don't need a do loop at all:

And PROC REPORT would do this too. But I would probably start with PROC MEANS.

Using SQL, you don't need a do loop, but you do need to know the names of the variables:

proc sql;
  select sum(age) as agesum, sum(height) as htsum, sum(weight) as wtsum
  from sashelp.class;
quit;

Hope this helps,

Cynthia

Re: how to sum all numeric variables in sql

s_lassen — Mon, 28 Jan 2019 08:55:30 GMT

I think @Cynthia_sas already gave you a good answer, but you should also know that there is no such thing as a "do loop" in SQL. This is because SQL is a declarative language (you tell the computer what you want), rather than a procedural language (you tell the computer what to do) like SAS datastep or macro language.