Your SAS programs, embedded in web apps and elsewhere

The following columns were not found in the contributing tables

Reply
Occasional Contributor
Posts: 5

The following columns were not found in the contributing tables

proc sql noprint;
connect to odbc as sqldb(&odbc_conn);
create table CHCSS as
select
'Project'n label='Project' length=200 format=$200. informat=$200.,
'site'n label='Site',
'Subject'n label='Subject',
'UniqueIdentifier'n label='UniqueIdentifier' length=200 format=$200. informat=$200.,
'CHCSCDAT_INT'n label='Health care contact (Non-asthma) Date'
from connection to sqldb (
select
Project as Project,
SUBSTRING(Site,1,4) as Site,
Subject as Subject,
project + '-' + site + '-' + subject + '-' + LTRIM(RTRIM(InstanceName)) + '-' + cast(INSTANCEREPEATNUMBER as varchar(10)) + '-' + cast(RECORDPOSITION as varchar(10)) + '-' + cast(INSTANCEID as varchar(10)) + '-' + cast(FOLDERNAME as varchar(100)) + '-' + cast(DATAPAGEID as varchar(10)) + '-' + cast(RECORDID as varchar(10)) as 'UniqueIdentifier',
Convert(varchar(10),CHCSCDAT_INT,21) as 'CHCSCDAT_INT'
from dbo.V_D3250C00018_CHCSS
where environmentname='PROD' and userid=57);
disconnect from sqldb;
quit;

 

Here Unique identifier is not part of the V_D3250C00018_CHCSS table somehow we need to get the data for this column as combination of the other columns.Please suggest me whats need to be incurred in this program to avoid the error.

Super User
Posts: 7,824

Re: The following columns were not found in the contributing tables

a) which "following columns" were not found? Please the log of the complete SQL step

b) you don't need the '.....'n around the names in the SAS, and the single quotes in the SQL passthrough, as all are valid names already.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 5

Re: The following columns were not found in the contributing tables

Posted in reply to KurtBremser

The uniqueidentifier columns were not found in the contributing tables this was the error message

Super User
Posts: 7,824

Re: The following columns were not found in the contributing tables

Then I'd try to run the subquery in the native "sqldb" environment to see what happens there.

Or pull the creation of the uniqueidentifier over into SAS (as LinusH recommmended), so the pass-through SQL is kept as simple as possible.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 5

Re: The following columns were not found in the contributing tables

Posted in reply to KurtBremser

when i am executing in SAS Editor it is giveing proper results but when im trying to execute through batch file it throws the error mentioned by me above.

Super User
Posts: 7,824

Re: The following columns were not found in the contributing tables

Your batch may run in a different context than your interactive SAS (other user, other autoexec & config, missing system environment variables etc) with regards to the DB access

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 5

Re: The following columns were not found in the contributing tables

Posted in reply to KurtBremser

No other direct fetch from the DB is fetching correctly through batch as well but since in this that UniqueIdentifier is a derived col there is something wrong which im unable to figure it off.i think the configurations and the user settings is correctly created according to the context.

Super User
Posts: 7,824

Re: The following columns were not found in the contributing tables

Fetch all columns as is in the pass-through and create the unique identifier in SAS with SAS means.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 7,973

Re: The following columns were not found in the contributing tables

What does this return to SAS, look at the properties of the dataset:

proc sql noprint;
  connect to ODBC as SQLDB(&ODBC_CONN);
  create table CHCSS as
  select *
  from CONNECTION TO SQLDB (
    select  Project,
            SUBSTRING(Site,1,4) as Site,
            Subject as Subject,
            project + '-' + site + '-' + subject + '-' + LTRIM(RTRIM(InstanceName)) + '-' + cast(INSTANCEREPEATNUMBER as varchar(10)) + '-' + cast(RECORDPOSITION as varchar(10)) + '-' + cast(INSTANCEID as varchar(10)) + '-' + cast(FOLDERNAME as varchar(100)) + '-' + cast(DATAPAGEID as varchar(10)) + '-' + cast(RECORDID as varchar(10)) as UniqueIdentifier,
            Convert(varchar(10),CHCSCDAT_INT,21) as CHCSCDAT_INT
    from    dbo.V_D3250C00018_CHCSS
    where   environmentname='PROD' and userid=57);
  disconnect from sqldb;
quit;

Note, you can use the {i} above a post to keep formatting on code - makes it far easier to read.  Also note, I took the quotes from around UniqueIdentifier and CHCSCDAT_INT, not sure these are correct.  The log would also help.

Super User
Posts: 5,431

Re: The following columns were not found in the contributing tables

You could try to use implicit pass thru. This would simplify your code, and make trouble shooting easier.

Most of your cast operations may be done automatically by the SAS libname engine.

Data never sleeps
Occasional Contributor
Posts: 5

Re: The following columns were not found in the contributing tables

what does implicit pass thru can u negotiate with program example since am sas beginner

Super User
Posts: 5,431

Re: The following columns were not found in the contributing tables

Instead of writing the connect to, and select ... from connection to(), just issue a libname statemnt that has the corresponing inforamtion that you hve in your connet to clause. See online doc for syntax/examples.

libname sqldb your oledb connection options goes here;

proc sql;
   create table CHCSS as
      select project, substr(site,1,4) as site, subject.....
      from sqldb.V_D3250C00018_CHCSS
      where environmentname='PROD' and userid=57
   ;
quit;
Data never sleeps
Ask a Question
Discussion stats
  • 11 replies
  • 1410 views
  • 0 likes
  • 4 in conversation