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?
Read in detail the SAS Docu here and then eventually ask questions if the docu is not clear enough.
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;
Thanks!
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.
Yes, to get the cross product of two tables,
select * from A, B;
/* is equivalent to */
select * from A cross join B;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: