BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
AlonzoT
Calcite | Level 5

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;
1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

@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;

View solution in original post

4 REPLIES 4
Tom
Super User Tom
Super User

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
Super User

@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
Calcite | Level 5

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
Super User

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.

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

5 Steps to Your First Analytics Project Using SAS

For SAS newbies, this video is a great way to get started. James Harroun walks through the process using SAS Studio for SAS OnDemand for Academics, but the same steps apply to any analytics project.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 4 replies
  • 407 views
  • 0 likes
  • 3 in conversation