Calculate a value by making mathematical calculations on different columns in different tables using SQL?

MustafaAbdelhaq — Sun, 31 May 2015 12:14:31 GMT

Hello,

So I have multiple tables with only one record in each one of them. I have another table which combines all the tables in one table.

What i'm trying to do is to calculate a value from multiple tables (subtract and add) to then create a new column with the result.

Your help is appreciated .

Re: Calculate a value by making mathematical calculations on different columns in different tables using SQL?

PGStats — Mon, 01 Jun 2015 02:13:20 GMT

You could use SASHELP.VTABLE and call execute to generate the SQL queries:

/* Create three example tables */

data table_A table_B table_C;

do x = 1,2,3; output; end;

run;

data _null_;

length oldTable newTable $41;

call execute("proc sql;");

do until (done);

/* Get the table names */

set sashelp.vtable(

where=(libname="WORK" and memname like "TABLE_%")) end=done;

oldTable = cats(libname, ".", memname);

/* Create the new table name from the old one */

newTable = cats(oldTable, "_new");

/* Create the query with the definition of the new variable */

call execute(catx(" ", "create table", newTable, "as select *, x+1 as y from", oldTable, ";"));

end;

call execute("quit;");

stop;

run;

/* Execution of the SQL procedure will occur here */