SAS Invalid data set name

[Tater_Salad]

Hi,

We're using SAS V 9.04.01M4P110916

I'm accessing Data from a defined Library called:

%let us_mkt = server='sandntz.bkrgc.net' database='IDP_PRD_US_MARKETING' port=5480 user=&db2_idx. password=&win_pwdx.

libname ROLAP netezza &us_mkt. schema=USER_ROLAP

data ROLAP.USER_ROLAP.SALES_TBL;
set ROLAP.SALES_TBL;
run;

I'm getting the following errror message:

263 data ROLAP.USER_ROLAP.SALES_TBL;
______________________________
211
ERROR 211-185: Invalid data set name.

264 set ROLAP.SALES_TBL;
265 /* set USER_ROLAP.SALES_TBL; */
266 run;

If ROLAP is the correctly defined library and USER_ROLAP is the correctly defined schema. Why am I getting this error? Also FYI: Any other combination of these names gives an error where the program won't read data. I understand that ROLAP.USER_ROLAP is redundant but if I just say ROLAP.SALES_TBL in the data statement: I get the following error. Here is how the documentation says I should define it.

263 data ROLAP.SALES_TBL;
264 set USER_ROLAP.SALES_TBL;
ERROR: Libref 'USER_ROLAP' exceeds 8 characters.
ERROR: File WORK.SALES_TBL.DATA does not exist.
265 /* set USER_ROLAP.SALES_TBL; */
266 run;

Why am I getting these errors?

[sidpesar · Accepted solution]

1.  data ROLAP.USER_ROLAP.SALES_TBL;
    I think there is double dot in create data set statement
2. 
Can you please use 
data work.SALES_TBL;
SET ROLAP.SALES_TBL;
RUN;

[Tater_Salad]

That did it!

Can you guys tell I'm brand spanking new to SAS. I was only going with what worked. and not what was text book. I'm sort of 'feeling' my way through this with mixed results. 🙂

so it looks like the data statement is saying the following 

data work.SALES_TBL; "CREATE a data set called SALES_TBL and put it in the WORK General Library."

SET ROLAP.SALES_TBL; "Get the Data for WORK.SALES_TBL from the existing table called SALES_TBL located in ROLAP Library."

RUN; Run it.

 I'm assuming that's how it reads. Tell me if I'm wrong.

[Kurt_Bremser]

The schema is already part of the libname. Use only

libname.dataset

in the data statement.

[SASKiwi]

A DATA statement like this: data ROLAP.USER_ROLAP.SALES_TBL; is invalid. You can only have a two-stage name like:  

 data ROLAP.SALES_TBL;

Please explain what you are trying to do with this program as it looks like you are reading from a Netezza table then writing the same data back to it which doesn't make sense.

[Tater_Salad]

Basically because I have absolutely no idea what I'm doing....

Read how I figured it out in my reply above

[SuryaKiran]

data ROLAP.USER_ROLAP.SALES_TBL;
set ROLAP.SALES_TBL;
run;

For the above you don't need to mention your schema again as 2nd level name, you already created a Library (using LIBNAME statement ROLAP) that is pointing to that schema USER_ROLAP. ie. When ever your first level name (here ROLAP) is mentioned then it points to that particular server and schema when mentioned in Libname statement. SAS has one level or two level library reference, but you mentioned 3 level name here which is invalid. Check this document 

263 data ROLAP.SALES_TBL;
264 set USER_ROLAP.SALES_TBL;
ERROR: Libref 'USER_ROLAP' exceeds 8 characters.
ERROR: File WORK.SALES_TBL.DATA does not exist.
265 /* set USER_ROLAP.SALES_TBL; */
266 run;

For the second one you mentioned USER_ROLAP as your library which is never defined and SAS libraries must be 8 characters.

The below code works assuming you have write access( replacing the same data to same source)

data ROLAP.SALES_TBL;
set ROLAP.SALES_TBL;
run;

If you want to copy the data to SAS temporary location then you can either give one level name or two level name.

data SALES_TBL; /* One level name, creating temp table in WORK library*/
set ROLAP.SALES_TBL;
run;

data WORK.SALES_TBL; /* two level name, creating temp table in WORK library*/
set ROLAP.SALES_TBL;
run;

/* Both are same */