BookmarkSubscribeRSS Feed
HeatherNewton
Quartz | Level 8
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?

6 REPLIES 6
Patrick
Opal | Level 21

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

PGStats
Opal | Level 21

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;
PG
HeatherNewton
Quartz | Level 8

Thanks!

Tom
Super User Tom
Super User

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
Opal | Level 21

Yes, to get the cross product of two tables,

 

select * from A, B;

/* is equivalent to */

select * from A cross join B;
PG
Reeza
Super User
Filtered cross product which essentially becomes an inner join.
Not the best way to program IMO.

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 6 replies
  • 965 views
  • 2 likes
  • 5 in conversation