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 ?
~= 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.
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
@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".
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.
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;
😁
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.