sas >> Joining tables

by Si » Thu, 20 Jan 2005 15:34:38 GMT

Folks - help with a simple one please...

I have two tables A and B. Each contains only one column called ID.

A will have some ID values not found in B. B will also have some ID values
not found in A.

All I want to do is run through A writing a binary flag (onto A) to signal
whether this ID is found in B.

I can do this in two stages, but presumably there's a neatear way.

My way is to cut B down to size using a proc sql such that B only contains
IDs that are in A. Then I do a merge writing a flag if my two in
conditions both fire. This rather convoluted method gets me around the
problem of finding extra rows in A if I don't subset B first.

Can someone show me the neater way please? I'm assuming proc sql is the
best way but a merge will do just as well.

To recap, if I have 100 rows in A and 50 in B, I still want the original 100
rows in a once the merge has been done. I _do not_ want any extra rows in
A thanks to the merge.

Cheers
Si





sas >> Joining tables

by jim1stat » Thu, 20 Jan 2005 17:39:24 GMT


Hi Herby,

A quick one data step solution is (given both A and B sorted according to
ID) (*untested*):

DATA AplusInB;
MERGE A (IN=InA) B (IN=InB); BY ID;
IF (InA);
FoundInB = InB;
RUN;

Regards - Jim.
[common disclaimer]




values
100



sas >> Joining tables

by omugeye » Thu, 20 Jan 2005 21:10:22 GMT

data a;retain flag 0;input id @@;cards;
1 2 3 4 5 6 7 8 9 10
;proc print data = a;
data b;input id @@;cards;
1 2 4 5 7 8 10
;proc print data = b;run;

proc sql;
update a set flag = 1 where id in (select id from b);
quit;
proc print data = a;
run;



Joining tables

by Scott » Thu, 20 Jan 2005 21:17:18 GMT

Hi,

There are others on this list whose SQL is far greater than mine. I came
about this solution by hacking around a bit, but it seems to work, with one
caveat:

data one;do id=1 to 20;output;end;id=.;output;run;
data two;do id=2 to 30 by 2;output;end;id=.;output;run;
data three;do id = "A","E","I","O","U"," ";output;end;run;
data four;do id="B","C","D","E","O"," ";output;end;run;
proc sql;
select one.*,(two.id is not null) as flag
from one left join two
on one.id = two.id
;
select three.*,(four.id is not null) as flag
from three left join four
on three.id = four.id
;
quit;

However, as you can see, this will fail if your ID is null (missing). I'd
be curious to know if SQL has an equivalent function to the data step IN=
option.

HTH,
Scott








Joining tables

by benpub7 » Sat, 22 Jan 2005 03:28:04 GMT

Why should SQL need 'in' options in data step? data step merge just one
small sub set of SQL.

I didn't quite uniderstand this posted question. I think you can use full
join in SQL and coalesce function .





On Fri, 21 Jan 2005 00:17:18 +1100, Scott


one


to

signal
contains
the


Joining tables

by Scott » Mon, 24 Jan 2005 05:43:46 GMT

Ben" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...

I don't understand this question either. The data step has nothing to do
with SQL.

data step merge just one

Actually, it's not part of SQL at all.


My question is, is there any way in SQL to determine which data set(s)
contributed an observation during a join? This would be analogous to the
IN= data set option.





Joining tables

by HERMANS1 » Mon, 24 Jan 2005 23:29:01 GMT

cott:
SQL provides a more general method of determining the source of a row in a
join of tables: a prefix with the name of the table. One can use the table
name in the simplest case, but would probably want to create an alias. For
example, in

... select coalesce(t1.ID,t2.ID) as ID,t1.date as date1,t2.date as date2
from tbl1 as t1 full join tbl2 as t2
on t1.ID=t2.ID ...

t1 acts as an alias for tbl1 and t2 for tbl2. In fact, the SAS MERGE
falls far short of SQL in its ability to handle so-called 'projections' of
column variables when combining data. Note that columns selected can also
have aliases.
Sig

-----Original Message-----
From: SAS(r) Discussion [mailto: XXXX@XXXXX.COM ] On Behalf Of Scott
Sent: Sunday, January 23, 2005 4:44 PM
To: XXXX@XXXXX.COM
Subject: Re: Joining tables


"Ben" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...

I don't understand this question either. The data step has nothing to do
with SQL.

data step merge just one

Actually, it's not part of SQL at all.


My question is, is there any way in SQL to determine which data set(s)
contributed an observation during a join? This would be analogous to the
IN= data set option.



Joining tables

by nospam » Fri, 28 Jan 2005 12:03:40 GMT

oreover, you can inspect the prefixed keys in a CASE expression. This is
almost like having an IN= option. It gets ambiguous if you have a null key
(which of course Sig and numerous others will tell you is something to be
avoided).

Try this:

data one;
do key = .,1,2,4; output; end;
run;

data two;
do key = 1,2,3; output; end;
run;

proc sql;
select
coalesce(one.key,two.key) as key,
case when one.key is not null then 1 else 0 end as in_one,
case when two.key is not null then 1 else 0 end as in_two
from one full join two on one.key=two.key;
quit;

Output:

key in_one in_two

. 0 0
1 1 1
2 1 1
3 0 1
4 1 0

On Mon, 24 Jan 2005 10:29:01 -0500, Sigurd Hermansen < XXXX@XXXXX.COM >
wrote:

the
rows


Similar Threads

1. joining tables- one table below another

    Hello, let say I have 2 tables such as

           a        b       c
        ----------------------        ------->table1

          34      12
          35      14
          36               23


          a        b        c
         -------------------     ---------->table2
        34                 6
        35       12     16
        36        5       4

I want join these tables such  "one table below another table" using
PROC SQL statements. For the above tables ,I want to do it like that:

           a        b       c
        ----------------------
          34      12
          35      14
          36               23
          34                6
          35     12      16
          36      5        4

How can I do that using PROC SQL statements?Thanks a lot.

2. PROC SQL, How to streamline joining tables?

3. sql join tables

I have Table "T" and Table "R"
when I merge the two tables I only need to see what's left in R after
the merge (excluding cartesian product)

4. join tables with low high

5. Joining tables problem

hi all,

i have a simple problem in joining tables. my code is this:

PROC SQL;
      CREATE TABLE WORK._BIL_TERM_LOAN AS
      SELECT
        A.SAS_SOC_CDE,
        A.DUE_DTE,
        CASE
          WHEN B.NEW_REF_NO NE "" THEN
            B.NEW_REF_NO
          ELSE
            A.REF_NO
          END AS REF_NO,
        A.ACC_NO,
        A.ACC_NO_INTERNAL,
        A.FCYFLUCT,
        A.FCYBRTYP,
        A.INTREVED,
        A.ACC_PRINCIPAL_PAID_AMOUNT,
        A.REPAYMENT_TERM,
        A.USANCE_DAY,
        A.LOFCBALN,
        A.LOFBBALN,
        A.CCY_CDE,
        A.LOHKCUSL,
        A.LOHKBANL,
        A.ACCINT_FL,
        A.ACCINT_FL_CUST,
        A.SUSINT_FL,
        A.INTERATE,
        A.REPAYMTD,
        A.NOFINSTL,
        A.REPAYPER,
        A.REPAYAMT,
        A.INT_RATE_SIGN,
        A.FCYRLOAD,
        A.RECORD_STATUS,
        A.EUSANCEF,
        A.CREATE_DATE,
        A.HIBOR_RATE_SIGN,
        A.FCYRLOAD_HIBOR,
        A.GOV_GUAR_RATIO,
        A.REF_NO AS OLD_ACC_NO,
        A.LAST_UPDTE_DTE
      FROM HDATA.BIL_TERM_LOAN A
      LEFT JOIN MAP.BLS_MAPPING B
        ON
          A.REF_NO = B.OLD_REF_NO
      ;
    QUIT;




now i want the code to join tables even if 2 or more variables are missing
as long ast its not the joining key (REF_NO). pls help!!!

6. SQL Left Join Warning - Join 3 tables

7. PROC SQL technique for joining tables with some one-to-many.

8. Joining SAS Table and SQL Server Table