07-27-2017 11:54 AM
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 ;
Select * from TD.mytable;
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!
07-27-2017 12:02 PM
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.
07-27-2017 12:22 PM
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.
07-27-2017 12:25 PM
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?
07-27-2017 12:30 PM
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?
07-27-2017 02:39 PM
07-27-2017 01:03 PM
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.
07-27-2017 02:49 PM
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.