topic Re: looks like join but not in SAS Programming

looks like join but not

HeatherNewton — Mon, 21 Nov 2022 03:45:07 GMT

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?

Re: looks like join but not

Patrick — Mon, 21 Nov 2022 03:55:06 GMT

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

Re: looks like join but not

PGStats — Mon, 21 Nov 2022 04:13:06 GMT

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;

Re: looks like join but not

HeatherNewton — Mon, 21 Nov 2022 13:54:38 GMT

Thanks!

Re: looks like join but not

Tom — Mon, 21 Nov 2022 14:32:01 GMT

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.

Re: looks like join but not

PGStats — Mon, 21 Nov 2022 18:45:52 GMT

Yes, to get the cross product of two tables,

select * from A, B;

/* is equivalent to */

select * from A cross join B;

Re: looks like join but not

Reeza — Mon, 21 Nov 2022 18:47:47 GMT

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