Help using Base SAS procedures

proc sql question

Reply
Frequent Contributor
Posts: 105

proc sql question

I am trying to join two tables together via proc SQL in which have some identical columns (same name & same type) and some unique columns. I know there is a way to do this is regular SQL, I believed it was natural join, but I can't seem to run it without errors in SAS. Does anyone know the proper method for doing this via Proc sql ?

This is my current code:

proc sql;

  create table U2_Deposits_Merged_Nov_Feb as

    select month_end, ID, SubProductCode, OpenDate, DateClosed, Nov_Balance, Feb_Balance,

    Over_250k_CountNov, Under_250k_CountNov, Nov_Total, Over_250k_CountFeb,

    Under_250k_CountFeb, Feb_Total, OpenedSinceNov,

    (Nov_Total - Feb_Total + OpenedSinceNov) as Accounts_Closed

  from runoff.U2_Deposits_November

    natural join runoff.U2_Deposits_February

  on U2_Deposits_November.ID = U2_Deposits_February.ID;

quit;

(month_end, ID, productcode, opendate, dateclosed are common to both tables, and I would like them to be just be appended in the joined table, and the others are unique to each, those ending in 'Nov' belong solely to the November table, and those ending in 'Feb' belong only to the february table.)

Thank you so much!

Contributor
Posts: 65

Re: proc sql question

From the documentation (9.3):

"Do not use an ON clause with a natural join. An ON clause will cause a natural join to fail. When using a natural join, an ON clause is implied, matching all like columns."


Natural join will match on ALL identically-named columns. Your example has an ON clause which uses differently-named columns. It looks like you're trying to do a regular inner join.

Karl

Frequent Contributor
Posts: 105

Re: proc sql question

I've updated my code to:

proc sql;

  create table U2_Deposits_Merged_Nov_Feb as

    select month_end, ACCT, ClosedSince_Nov, Nov_Balance, U2_Deposits_February.Feb_Balance,

    U2_Deposits_November.Over_250k_CountNov, U2_Deposits_November.Under_250k_CountNov, U2_Deposits_November.Nov_Total, U2_Deposits_February.Over_250k_CountFeb,

    U2_Deposits_February.Under_250k_CountFeb, U2_Deposits_February.Feb_Total

  from runoff.U2_Deposits_November

    natural join runoff.U2_Deposits_February;

quit;

However the resulting table has 0 rows.

Contributor
Posts: 65

Re: proc sql question

I think you're going to need to give us a little more info. The fact you're getting 0 rows implies that, while your tables may share columns with the same name and type, those columns do not share the same VALUES.

Can you post a couple rows of data from the 2 tables?

Frequent Contributor
Posts: 105

Re: proc sql question

Unfortunately I can not share rows of actual data as it is private information, however I can guarantee that there are thousands of rows with the same month_end and ACCT variables. I will try doing a full join. Thank you for all of the feedback

PROC Star
Posts: 1,093

Re: proc sql question

You'll need to specify all of your columns in the join, and remove the "natural" specification. From the SAS documentation:

"Do not use an ON clause with a natural join. An ON clause will cause a natural join to fail. When using a natural join, an ON clause is implied, matching all like columns."

Tom

Ask a Question
Discussion stats
  • 5 replies
  • 197 views
  • 0 likes
  • 3 in conversation