How to Join 2 excel tables using inner join

[AlonzoT]

I'm trying to inner join two tables imported from Excel both tables have the same SIN data set and I keep getting

ERROR: Column SIN could not be found in the table/view identified with the correlation name ST.
ERROR: Column SIN could not be found in the table/view identified with the correlation name ST.
ERROR: Column SIN could not be found in the table/view identified with the correlation name CT.
ERROR: Column SIN could not be found in the table/view identified with the correlation name CT.
ERROR: Column Credits could not be found in the table/view identified with the correlation name CT.

both excel tables have a SIN data set

CODE:

proc sql; 

select st.*, ct.Credits

from work.'students table'n as st

inner join

work.'course table'n as ct

on st.SIN = ct.SIN

;

quit;

[Tom]

The error message is pretty clear, neither dataset has a variable named SIN.

Run PROC CONTENTS on the datasets and check what the names of the variables are.

[ballardw · Accepted solution]

---

@AlonzoT wrote:

I'm trying to inner join two tables imported from Excel both tables have the same SIN data set and I keep getting

ERROR: Column SIN could not be found in the table/view identified with the correlation name ST.
ERROR: Column SIN could not be found in the table/view identified with the correlation name ST.
ERROR: Column SIN could not be found in the table/view identified with the correlation name CT.
ERROR: Column SIN could not be found in the table/view identified with the correlation name CT.
ERROR: Column Credits could not be found in the table/view identified with the correlation name CT.

both excel tables have a SIN data set

CODE:

proc sql; 

select st.*, ct.Credits

from work.'students table'n as st

inner join

work.'course table'n as ct

on st.SIN = ct.SIN

;

quit;

---

You are not referencing a "dataset" SIN. Excel does not have "tables" per se, sheets yes.

You are referencing a variable SIN. Just because a column heading in a spreadsheet appears to be some text does not mean that when you import/ read the data into SAS that the column heading is the name of the variable. Some column headings have multiple rows in a single cell so would likely be some longer form. Others may have characters other than letters that appear before the column heading and would likely, given your example above that implies you may be using Validvarname=any, the variable name may in fact be something like " SIN"n.

Or, if the column "heading" was not on the very first row of the spreadsheet could be something quite different.

Proc Contents will tell you the actual variable information:

Proc contents data= work.'students table'n;
run;

Proc contents data=work.'course table'n;
run;

[AlonzoT]

Thanks, this was helpful. The variable name was F2.

As a beginner in SAS, some things can be confusing. I appreciate you taking the time to explain and assist me. Thank you for your help.

[ballardw]

I missed a couple more that you may need to keep in mind: Hidden rows above the text you see as a column heading can make you think a column has a different name that actually happens. Hidden columns can bring in variables that you did not expect to see at all.

For what it is worth, I save anywhere from 15 to 50 spreadsheet files to CSV every week and read the files with data steps to prevent the headaches that result of "import" of spreadsheet files. Most of the headaches come from the nature of spreadsheets not having anything resembling a structure at all. A single "column" can contain text, numeric values, formulae, links to other values or look up. And Import be default uses very few cells to guess the properties that should apply to an entire column.