BookmarkSubscribeRSS Feed
AllSoEasy
Obsidian | Level 7

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!

5 REPLIES 5
KarlK
Fluorite | Level 6

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

AllSoEasy
Obsidian | Level 7

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.

KarlK
Fluorite | Level 6

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?

AllSoEasy
Obsidian | Level 7

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

TomKari
Onyx | Level 15

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 756 views
  • 0 likes
  • 3 in conversation