Proc SQL returning 0 rows from a view

[johnm_1971]

I'm having issues with proc sql returning results from a view.  When I execute the proc sql statement in teradata SQL assistant, rows are returned but inside of SAS it returns 0 rows.  My SAS proc sql statement is basic :

LIBNAME TD TERADATA tdpid = "xx.x" SCHEMA=myschema
USER=USER1 PASSWORD="xxxxx" SQLGENERATION=DBMS ;

Proc Sql;

         Select * from TD.mytable;

Quit;

Any thoughts on what is going on here.  I've used this similar command successfully many times before on other views in the environment.  Thanks!

[Reeza]

What does the full log show?

[johnm_1971]

The log does not show any error messages, just that the table is successfully created with 0 rows.

[Reeza]

---

@johnm_1971 wrote:

The log does not show any error messages, just that the table is successfully created with 0 rows.

---

You're not creating a table in the code you've shown, so that message doesn't make sense. My first guess is the libname doesn't work and your password on the server needs to get reset. So I would check my dsn connection first and see if the libname is assigned correctly, and if it is, you can navigate to the library and see your data in the folders.

[johnm_1971]

Sorry I skipped the create table line when copying my proc sql statement.  If I change my proc sql to select from another view without changing or re-executing my libname and run the code, it does return rows.  I'm just really stumped why for this one specific view SAS does not seem to be able to access the data. 

[Reeza]

1. Check length of table name, if it's over 32 chars that's it

2. Check case sensitivity, it's sometimes an issue you'll find, but not very often. 

3. Verify there's data in the table on the server side, is it possible the data isn't refreshed or was deleted for some reason?

[Shmuel]

You are trying to read through view TD.mytable.

1) How is the view defined ? are there and conditional selections inside the view?

2) Can it be thay mytable in TD is empty while you are focosed on other schema?

[Reeza]

@Shmuel how do you know it's a view versus a table?

[Shmuel]

---

@Reeza wrote:

@Shmuel how do you know it's a view versus a table?

---

@Reeza,

   it is taken from the topic name and the 1st phrase in the original post:

I'm having issues with proc sql returning results from a view.

[johnm_1971]

I don't have access to the view creation, but I do know that there is conditional logic that are applied in the view definition. Do we need the view to be created without any conditional logic?  I haven't encountered this being an issue before using other views with conditional definitions.

[Shmuel]

Using a view that was created on an original table (or on other view)

you are limited to the results of the view:

- only selected variables from origin

- only those observations fitting the conditions

- added calculated new variables

If you are not aware of the view definition you might get wrong results.