DATA Step, Macro, Functions and more

Proc SQL returning 0 rows from a view

Reply
New Contributor
Posts: 4

Proc SQL returning 0 rows from a view

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!

Super User
Posts: 17,912

Re: Proc SQL returning 0 rows from a view

What does the full log show?

New Contributor
Posts: 4

Re: Proc SQL returning 0 rows from a view

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

Super User
Posts: 17,912

Re: Proc SQL returning 0 rows from a view


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.

New Contributor
Posts: 4

Re: Proc SQL returning 0 rows from a view

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. 

Super User
Posts: 17,912

Re: Proc SQL returning 0 rows from a view

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?

Trusted Advisor
Posts: 1,401

Re: Proc SQL returning 0 rows from a view

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?

Super User
Posts: 17,912

Re: Proc SQL returning 0 rows from a view

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

Trusted Advisor
Posts: 1,401

Re: Proc SQL returning 0 rows from a view


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.
New Contributor
Posts: 4

Re: Proc SQL returning 0 rows from a view

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.

Trusted Advisor
Posts: 1,401

Re: Proc SQL returning 0 rows from a view

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. 

Ask a Question
Discussion stats
  • 10 replies
  • 160 views
  • 1 like
  • 3 in conversation