I’m running a query against Teradata and I’m able to see the results when I run the query in my database enviorment. When I run the sql script in SAS the query runs but it does not display the results in output data. The weird thing about this is I’m able to run other queries with no problems. But this query will not output the data results.
My Script
proc sql ;
connect to teradata (user='username' pass='pw' tdpid=TD1) ;
create table mydata as
select * from connection to teradata
(select *
from schema.V_Demographic_Data
) ;
disconnect from teradata ;
quit ;
We found the issue. It had to do with case sensitivity on the where clause for our view. It originally read, WHERE Marked_For_Deletion = 'n'. After changing it to WHERE Marked_For_Deletion = 'N', it works fine.
So when this runs, you don't see anything in WORK.MYDATA? If EG does not add the table to your output view, can you still navigate to WORK.MYDATA from File->Open? What does the SAS log say? Does it say that a table was created?
Chris
Hi Chris,
It created my table structure in WORK.MYDATA with the colum headers but no data. It should come back with 41,000 rows.
Log Results
1 ;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;
3 %LET _CLIENTTASKLABEL='Demo';
4 %LET _CLIENTPROJECTPATH='';
5 %LET _CLIENTPROJECTNAME='';
6 %LET _SASPROGRAMFILE=;
7
8 ODS _ALL_ CLOSE;
9 OPTIONS DEV=ACTIVEX;
NOTE: Procedures may not support all options or statements for all devices. For details, see the
documentation for each procedure.
10 GOPTIONS XPIXELS=0 YPIXELS=0;
11 FILENAME EGSR TEMP;
12 ODS tagsets.sasreport12(ID=EGSR) FILE=EGSR STYLE=Analysis
12 ! STYLESHEET=(URL="file:///C:/Program%20Files/SAS/EnterpriseGuide/4.3/Styles/Analysis.css")
12 ! NOGTITLE NOGFOOTNOTE GPATH=&sasworklocation ENCODING=UTF8 options(rolap="on");
NOTE: Writing TAGSETS.SASREPORT12(EGSR) Body file: EGSR
13
14 GOPTIONS ACCESSIBLE;
15 proc sql ;
16 connect to teradata (user='username' pass=XXXXXXXXX tdpid=TD1) ;
17 create table mydata as
18 select * from connection to teradata
19 (select *
20 from PUTL_SMART_HOURS_ASSESSMENT.V_Demographic_Data
21 ) ;
NOTE: Table WORK.MYDATA created, with 0 rows and 6 columns.
22 disconnect from teradata ;
23 quit ;
NOTE: PROCEDURE SQL used (Total process time):
real time 1.41 seconds
cpu time 0.00 seconds
24
25
26 GOPTIONS NOACCESSIBLE;
27 %LET _CLIENTTASKLABEL=;
28 %LET _CLIENTPROJECTPATH=;
29 %LET _CLIENTPROJECTNAME=;
30 %LET _SASPROGRAMFILE=;
31
32 ;*';*";*/;quit;run;
33 ODS _ALL_ CLOSE;
34
35
36 QUIT; RUN;
37
We found the issue. It had to do with case sensitivity on the where clause for our view. It originally read, WHERE Marked_For_Deletion = 'n'. After changing it to WHERE Marked_For_Deletion = 'N', it works fine.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.