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!
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
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.
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?
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
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
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.
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.