Complex Join in SAS

By : user2188464

Date : December 05 2020, 12:18 PM

it should still fix some issue If your data has the category value as an interspersed row you will need to create a third column to hold those values when discovered as you pass through the data set. For discussion name this new column group -- it will also be categorical and hierarchically 'above' the other category column. It is a 'synthetic' category that is needed for performing the complex join and will be discarded from the final result.
The want join will be a simple 'black box' involving grouping, coalescing, sneaky math and a group sum of a row sum.

code :

data testA;
input categorical $3. value;
datalines;
Dog .   * missing means categorical is really group
M  7
F  5
Cat .
M  4
F  2
Rat .   * B does not have rat
T  5
Bat .   * Bat has two M (repeated category) need to be summed
M  7
M  3
Fly .
M  5
F  6
;
run;

data testB;
input categorical $3. value;
datalines;
Dog .   * only one category
F  3
Cat .
M  1
F  2
Cow .   * A does not have cow
X  7
Bat .   * Bat has two F (repeated category) need to be summed
F  7
F  13
Fly .   * F M order different than A
F  16
M  20
;
run;

data A2;
  set testA;
  if value = . then do;
    * presume missing is the 'discovery' of when the 
    * group value has to be assigned;
    group = categorical; retain group;
    group_order + 1;  
    value_order = 0;
  end;
  value_order + 1;
  format group_order value_order 4.;
run;

data B2; 
  set testB;
  if value = . then do;
    * presume missing is the 'discovery' of when the 
    * group value has to be assigned;
    group = categorical; retain group; 
    group_order + 1;
    value_order = 0;
  end;
  value_order + 1;
  format group_order value_order 4.;
run;

* this full join shows how data matches up for the answer
* the answer will use grouping, coalescing, summing and adding;
proc sql;
  create table fulljoin_peek as
  select
    coalesce (A.categorical, B.categorical) as want_categorical
  , sum(A.value,B.value) as want_value format=4.
  , A.group as A_group
  , B.group as B_group
  , A.group_order as A_group_order
  , B.group_order as B_group_order
  , A.categorical as A_cat
  , B.categorical as B_cat
  , A.value as A_value
  , B.value as B_value
  , A.value_order as A_value_order
  , B.value_order as B_value_order
  from
    A2 as A
  full join 
    B2 as B
  on 
    A.group = B.group
    and A.categorical = B.categorical
;

proc sql;
  create table

    want (drop=group_order value_order) as

  select 
    coalesce (A.categorical, B.categorical) as want_categorical
  , min (coalesce (A.group_order-1e6,B.group_order)) as group_order
  , min (coalesce (A.value_order-1e6,B.value_order)) as value_order   %* -1e6 forces A order to have precedence ;
  , sum ( sum (A.value,B.value) ) as value
  from
    A2 as A
  full join 
    B2 as B
  on 
    A.group = B.group
    and A.categorical = B.categorical
  group by 
    A.group, want_categorical
  order by 
    group_order,  value_order
  ;

Share :

T-SQL - How to write a complex conditional join including a many-to-many join

By : Webmaker Madhu Panwa

Date : March 29 2020, 07:55 AM

Hope this helps I lean towards using dynamic sql in these situations, because using ORs to accommodate this logic are horrible for performance and sargability. The following example is intended for SQL Server 2005+:

code :

DECLARE @SQL NVARCHAR(4000)

SET @SQL = 'SELECT DISTINCT
                   p.personid,
                   p.personname,
                   c.city
              FROM TBLPERSON p
         LEFT JOIN TBLCITIES c ON c.personid = p.personid '

SET @SQL = @SQL + CASE 
                    WHEN @grouplist IS NOT NULL THEN
                      ' JOIN (SELECT m.PersonID
                                FROM TBLGROUPMEMBERSHIP m
                               WHERE m.GroupID IN (SELECT item FROM fnSplit(@grouplist, ',')))
                            GROUP BY m.PersonID
                              HAVING COUNT(*) = (SELECT COUNT(*) FROM fnSplit(@grouplist, ',')))) g ON g.personid = p.personid '
                    ELSE
                      ' '
                  END

  SET @SQL = @SQL + ' WHERE 1 = 1 ' --trick to make contatentating WHERE clause easier

IF @name IS NOT NULL
  SET @SQL = @SQL + ' AND p.personname LIKE '%' + @name + '% '

IF @city IS NOT NULL
  SET @SQL = @SQL + ' AND c.city LIKE '%' + @city + '% '  

BEGIN

  EXEC sp_executesql @SQL N'@grouplist varchar(50), @grouplist varchar(50), @name varchar(50), @city varchar(50)',
                      @grouplist, @grouplist, @name, @city

END

how to join after left join complex mysql queries

By : whoami

Date : March 29 2020, 07:55 AM

Any of those help The three conditions in the join (the AND clauses) might be giving you trouble. Those three conditions are selection criteria, not join criteria.
Also, your use of CASE looks odd to me. I'm sure it works, but IF might be better suited for a one-condition function. In the below, if the fields are floating point rather than integer then replace the 0 with 0.0.

code :

SELECT currency_code, 
    SUM(IF(TYPE = 'buy', to_amount, 0)) AS BUY,
    SUM(IF(TYPE = 'sell', to_amount, 0)) AS SELL, 
    SUM(IF(TYPE = 'sell', rate, 0)) AS SELL_RATE,
    SUM(IF(TYPE = 'buy', rate, 0)) AS BUY_RATE,
    AVG(IF(TYPE = 'buy', rate, 0)) AS AVG_BUY_RATE,
    AVG(IF(TYPE = 'sell', rate, 0)) AS AVG_SELL_RATE,
    tb_user.whatever_field,
    tb_user.whatever_other_field
FROM tb_currency
    LEFT JOIN tb_bill ON tb_currency.CURRENCY_ID = tb_bill.CURRENCY_ID
    LEFT JOIN tb_user ON tb_bill.user_id = tb_user.user_id
WHERE tb_bill.TYPE IN ('buy', 'sell')
    AND date( DATE_TIME ) >= '2011-01-01'
    AND date( DATE_TIME ) <= '2011-01-11'
GROUP BY currency_code, tb_user.user_id

Complex SQL Join

By : Дмитрий Поленов

Date : March 29 2020, 07:55 AM

may help you . I am fairly new to SQL joins, but I have a tricky issue here. I have tried to resolve this on my own, and searched as well, but unsuccessful. , try this one:

code :

SELECT  a.CorpID,
        b.ClientName AS CorpIDClientName,
        a.DivID,
        c.ClientName AS DivIDName,
        a.DeptID,
        d.ClientName AS DeptIDName
FROM    CustTransaction a
            INNER JOIN CustProfile b
                on a.CorpID = b.ClientID
            INNER JOIN CustProfile c
                on a.DivID = c.ClientID
            INNER JOIN CustProfile d
                on a.DeptID = d.ClientID

Complex JOIN Query Where JOIN Table May NOT Have Records

By : konstantin.s

Date : March 29 2020, 07:55 AM

To fix the issue you can do If the data may not exist in the table you want to join with, then you want to use a LEFT JOIN:

code :

SELECT
     users.ID
    ,users.ggProjectId
    ,users.timezone
    ,users.ggTimestamp
    ,users.slug
    ,items.uID
FROM users
LEFT JOIN items
 ON users.ggProjectId = items.uID
WHERE 
    (
         users.eventdate <= DATE( NOW( ) + INTERVAL 2 DAY ) 
     AND users.eventdate >= DATE( NOW( ) - INTERVAL 2 DAY )
    ) OR ( 
         items.startingtime <= DATE( NOW( ) + INTERVAL 2 DAY ) 
     AND items.startingtime >= DATE( NOW( ) - INTERVAL 2 DAY )
    )
GROUP BY users.ggProjectId

SQL JOIN issue - complex SELECT as JOIN Condition

By : Sławomir Bodak

Date : March 29 2020, 07:55 AM

Does that help It's a bit unclear what it is you want to accomplish but maybe this is what you are looking for:

code :

SELECT MKey, Otherdata 
FROM M
LEFT OUTER JOIN (
    SELECT DISTINCT 
      LEFT(FUNKey, CHARINDEX ('/', FUNKey) -1) FUNKEY
    , BinaryData 
    FROM FUN
) F ON M.MKey = F.FUNKEY AND F.BinaryData = 1;

MKey                 Otherdata
-------------------- --------------------
ABCD                 kjjh
EFGH                 oioo
IJKL                 uhdjdhu
MNOP                 isdid
QRST                 lkuh

Related Posts :

sorting lists of lists in haskell

In Haskell, how can you sort a list of infinite lists of strings?