The following columns were not found in the contributing tables

[sravan1]

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.

[Kurt_Bremser]

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.

[sravan1]

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

[Kurt_Bremser]

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.

[sravan1]

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.

[Kurt_Bremser]

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

[sravan1]

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.

[Kurt_Bremser]

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

[RW9]

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.

[LinusH]

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.

[sravan1]

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

[LinusH]

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;