BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Tater_Salad
Obsidian | Level 7

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?

1 ACCEPTED SOLUTION

Accepted Solutions
sidpesar
Obsidian | Level 7
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;

 

View solution in original post

6 REPLIES 6
sidpesar
Obsidian | Level 7
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
Obsidian | Level 7

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.

SASKiwi
PROC Star

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
Obsidian | Level 7

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

 

Read how I figured it out in my reply above

SuryaKiran
Meteorite | Level 14

 

 

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 */
Thanks,
Suryakiran

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 6 replies
  • 3876 views
  • 0 likes
  • 5 in conversation