logo
down
shadow

SAS program, How to do the calculation based on two tables without IML


SAS program, How to do the calculation based on two tables without IML

By : Chhorvorn Loch
Date : November 20 2020, 03:01 PM
will be helpful for those in need Maybe I'm missing something but a cross join should also work fine with the GEODIST function and then a transpose to make it wide. It doesn't matter how many points are in each file, though I suppose if it got big enough SQL may error out due to memory.
code :
proc sql;
create table want as
select task_id as col1, id as col2, geodist(a.x, a.y, b.x, b.y) as distance
from tasks as a , engineers as b;
quit;

proc transpose data=want out=want_wide ;
by col1;
id col2;
var distance;
run;


Share : facebook icon twitter icon
Combine SQL queries, joining tables based on a calculation

Combine SQL queries, joining tables based on a calculation


By : David Smith
Date : March 29 2020, 07:55 AM
I hope this helps you . Yes, you can join on those last 3 characters of the description column:
code :
UPDATE [tblManipulateD]
SET [tblManipulateD].[abbreviation] = RIGHT([tblManipulateD].[description],3), 
    [tblManipualteD].[product code] = [tblStorage].[storage product]
FROM [tblManipulateD] 
LEFT JOIN [tblStorage] ON RIGHT([tblManipulateD].[description],3) = [tblStorage].[storage abbreviation]
WHERE [tblManipulateD].[description] LIKE “*abbrev:*";
Calculation based on two tables

Calculation based on two tables


By : Kashif Akhlaq
Date : March 29 2020, 07:55 AM
this will help In Access 2010 and later you could use a Before Change data macro on [TableB] to derive the [B3] value like this:
Yes/No Calculation Based Off Of Other Tables Record

Yes/No Calculation Based Off Of Other Tables Record


By : Tausif Mujeeb
Date : March 29 2020, 07:55 AM
Any of those help If I'm understanding correctly, you want to use an update statement with a join:
code :
update tblContainers as c
   inner join tblRentals as r on c.container = r.container 
set c.IsRented = r.IsRented
Products' quantity calculation based on transactions stored in two different tables

Products' quantity calculation based on transactions stored in two different tables


By : Matic Ferjan
Date : March 29 2020, 07:55 AM
may help you . Looks like you will need to use inline views to aggregate quantity from the transactions table and the returned items tables
code :
SELECT i.id
     , i.quantity
     , IFNULL(t.quantity,0) AS t_quantity
     , IFNULL(r.quantity,0) AS r_quantity
     , i.quantity - IFNULL(t.quantity,0) + IFNULL(r.quantity,0) AS calc_qty
  FROM items i
  LEFT
  JOIN ( SELECT tt.id_articolo
              , SUM(tt.quantity) AS quantity 
          FROM transactions tt
         GROUP BY tt.id_articolo
       ) t
    ON t.id_articolo = i.id
  LEFT
  JOIN ( SELECT rr.id_articolo
              , SUM(rr.quantity) AS quantity 
          FROM returned_items rr
         GROUP BY rr.id_articolo
       ) r
    ON r.id_articolo = i.id
HAVING calc_qty > 0 
 ORDER BY i.id
SQL with calculation based on multiple tables

SQL with calculation based on multiple tables


By : Cheeksfish
Date : March 29 2020, 07:55 AM
I think the issue was by ths following , I have a simple DB with the following two tables.
shadow
Privacy Policy - Terms - Contact Us © voile276.org