Creating proc sql ON clause

[RAVI2000]

proc sql; 
  create table dadkid4  as
  select  *, (dads.famid=faminc.famid) as indic, 
             (dads.famid ~=.) as dadind,
             (faminc.famid ~=.) as famind,
             coalesce(dads.famid, faminc.famid) as fid
  from dads full join faminc on dads.famid=faminc.famid;
quit;

proc print data=dadkid4;
run;

can anyone briefly explain the above code?

how we use ~=. in SELECT ?

when do we use ON clause in FROM ?

diff bt ON and WHERE ?

[ballardw]

~=  is "not equal", you could use the NE as well. The comparisons are creating 1/0 coded values when the indicated variable is missing.

ON is used with JOIN. It is how the records from sets are compared to match the desired records.

WHERE is applied after records are joined. You can often get the same result as an ON but it may be much more inefficient to test after the records are combined.

[RAVI2000]

Thank you for the explanation. Still not very clear about ON clause. I understood it. But i would like to look or practice more examples. Can you share any ? Thanks

[ballardw]

---

@RAVI2000 wrote:

Thank you for the explanation. Still not very clear about ON clause. I understood it. But i would like to look or practice more examples. Can you share any ? Thanks

---

Search the SAS documentation for "JOIN". Most of the responses will SQL with ON somewhere in the mix.

Basically think "match records on these variables".

[ChrisNZ]

Once again, properly formatted code will answer most of the questions for you.

For example, isn't this a lot clearer?

proc sql;
  create table dadkid4  as
  select  *
        , (dads.famid = faminc.famid)        as indic 
        , (dads.famid ~= .)                  as dadind
        , (faminc.famid ~= .)                as famind
        , coalesce(dads.famid, faminc.famid) as fid
  from dads 
         full join 
       faminc 
         on dads.famid=faminc.famid;
quit;

There is no excuse for not formatting code.

[PGStats]

Using functions with meaningful names and comments for the not so obvious stuff also helps...

proc sql; 
  create table dadkid4 as
  select  *
        , dads.famid = faminc.famid          as indic  /* data is from both tables */
        , not missing(dads.famid)            as dadind /* has data from table dads */
        , not missing(faminc.famid)          as famind /* has data from table faminc */
        , coalesce(dads.famid, faminc.famid) as fid
  from dads 
         full join 
       faminc 
         on dads.famid=faminc.famid;
quit;

😁