05-31-2015 08:14 AM
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 .
05-31-2015 10:13 PM
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;
length oldTable newTable $41;
call execute("proc sql;");
do until (done);
/* Get the table names */
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, ";"));
/* Execution of the SQL procedure will occur here */