topic Re: Creating proc sql ON clause in SAS Programming

Creating proc sql ON clause

RAVI2000 — Wed, 22 Apr 2020 21:28:28 GMT

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 ?

Re: Creating proc sql ON clause

ballardw — Wed, 22 Apr 2020 21:56:37 GMT

~= 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.

Re: Creating proc sql ON clause

RAVI2000 — Thu, 23 Apr 2020 21:25:20 GMT

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

Re: Creating proc sql ON clause

ballardw — Thu, 23 Apr 2020 21:45:56 GMT

@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".

Re: Creating proc sql ON clause

ChrisNZ — Fri, 24 Apr 2020 21:54:47 GMT

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.

Re: Creating proc sql ON clause

PGStats — Fri, 24 Apr 2020 20:54:40 GMT

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;

😁