looks like join but not

[HeatherNewton]

proc sql;
      create table mthbal_bal as
      select mthbal_org as ambs_org,
                mthbal_acct as ambs_acct,
                mthbal_curr_bal as mthend_bal,
                mthbal_crlim,
                intnx("month", a.mthbal_load_Dt,0,"E") as mthend_date
      from mthbal a,
               acct_new_all b
      where a.mthbal_org=b.org_code
      and a.mthbal_acct=b.account_no;
quit;
 

what kind of program is this? looks like 'join' but without saying what the join is?

what is it doing?

[Patrick]

Read in detail the SAS Docu here and then eventually ask questions if the docu is not clear enough.

[PGStats]

This is standard alternative SQL syntax for simple joins. It is equivalent to:

proc sql;
      create table mthbal_bal as
      select mthbal_org as ambs_org,
                mthbal_acct as ambs_acct,
                mthbal_curr_bal as mthend_bal,
                mthbal_crlim,
                intnx("month", a.mthbal_load_Dt,0,"E") as mthend_date
      from 
		mthbal a inner join 
		acct_new_all b 
			on a.mthbal_org=b.org_cod and a.mthbal_acct=b.account_no;
quit;

[HeatherNewton]

Thanks!

[Tom]

The effect is similar to an inner join, but that is just because of the particular WHERE expression used.  

But it is a Cartesian product, every observation in each dataset is paired with every observation with the other dataset.  So a FULL JOIN  with an always true ON condition is more accurate description of the join type.  The results are then filtered by the WHERE condition which is what causes the result to be the equivalent of an INNER JOIN.

Note that is frequently the intent of the user when using that comma separator between the source dataset names to effect an inner join, but not always.  Sometimes you will use it to produce the Cartesian product.

[PGStats]

Yes, to get the cross product of two tables,

select * from A, B;

/* is equivalent to */

select * from A cross join B;

[Reeza]

Filtered cross product which essentially becomes an inner join.
Not the best way to program IMO.