BookmarkSubscribeRSS Feed
sravan1
Calcite | Level 5

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.

11 REPLIES 11
Kurt_Bremser
Super User

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

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

Kurt_Bremser
Super User

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

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

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

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Tourmaline | Level 20

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

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

LinusH
Tourmaline | Level 20

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

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 2980 views
  • 0 likes
  • 4 in conversation