BookmarkSubscribeRSS Feed
johnm_1971
Calcite | Level 5

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!

10 REPLIES 10
Reeza
Super User

What does the full log show?

johnm_1971
Calcite | Level 5

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

Reeza
Super User

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

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

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
Garnet | Level 18

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

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

Shmuel
Garnet | Level 18

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

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
Garnet | Level 18

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. 

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
  • 10 replies
  • 1981 views
  • 1 like
  • 3 in conversation