Hello-
I have a username that it similar to this: "MY-USER-NAME" and this is causing me issues when it comes to using it as a schema in my libname. I have tried creating a secondary variable that upcases and double quotes the username and passing that in, which allows me to assign the libname just fine, but once I attempt to use it in a data step i receive the below error.
User name secondary variable:
%let nUser = "%upcase(&user.)";
Then I successfully assign the libname:
libname nzp netezza user=&nUser. password="&nzpass" server="server.co.net" database="DBC" schema=&nUser. connection=shared; NOTE: Libref nzp was successfully assigned as follows: Engine: NETEZZA
However, attempting to create a dataset on Netezza with a data step causes this:
data nzp.mytable (bulkload=yes); set work.have; run; NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables. ERROR: Error attempting to CREATE a DBMS table. ERROR: CLI execute error: ERROR: 'USER-NAME.MYTABLE' error ^ found "-" (at char 0) expecting `AS'. NOTE: The DATA step has been abnormally terminated.
The issue appears to be that when the libname references the schema inside the data step, it is unquoted, thereby causing this error. Also to note, there are tables in Netezza currently that have this username as their schema, they simply show double quotes around the schema name.
Thank you in advance for any assistance!
A member of my company was able to help fix the issue for me
schema='"MY-USER-NAME"'
I've only gotten it to work by hard coding the username and surrounding the double-quoted username with single quotes.
can you try putting %quote or %str as following
libname nzp netezza user=%quote(&nUser.) password="&nzpass" server="server.co.net" database="DBC" schema=%quote(&nUser.) connection=shared;
libname nzp netezza user=%str(&nUser.) password="&nzpass" server="server.co.net" database="DBC" schema=%str(&nUser. ) connection=shared;
Let me know what works.
Try putting the quotes into the value you tell the LIBNAME statement to use for the SCHEMA.
libname nzp netezza ... schema="""USER-NAME""" ... ;
@jmspy wrote:
This one gave me a new error:
ERROR: Error attempting to CREATE a DBMS table. ERROR: CLI execute error: ERROR: zero-length delimited identifier.
NOTE: The DATA step has been abnormally terminated.
And what remote SQL code did SAS actually generate? Make sure to turn on the logging so you can see the generated code that SAS sends.
You should open a ticket with SAS support and get them to help you debug this.
There are many SAS options that control how much information is written to the SAS log. Here are some example settings I have used in the past.
options msglevel=i nostsuffix sastrace=',,,ds' sastraceloc=saslog sql_ip_trace=source;
Also try checking if you can access that schema using explicit pass thru code. For example if there is a table named MYTABLE in the USER-NAME schema can you run this query to count how many observations it has?
proc sql;
connect using nzp;
select * from connection to nzp
(select count(*) as nobs from user-name.mytable)
;
quit;
Here's the output with those additional log options:
NETEZZA: AUTOCOMMIT is NO for connection 1 NETEZZA_1: Executed: on connection 1 SELECT * FROM DBC.MY-USER-NAME.MYTABLE WHERE 0=1 Summary Statistics for NETEZZA are: Total SQL execution seconds were: 0.000619 Total seconds used by the NETEZZA ACCESS engine were 0.000782 NETEZZA_2: Prepared: on connection 0 SELECT * FROM DBC.MY-USER-NAME.MYTABLE ^L220 The SAS System 17:09 Tuesday, June 16, 2020 Summary Statistics for NETEZZA are: Total SQL prepare seconds were: 0.000824 Total seconds used by the NETEZZA ACCESS engine were 0.001073 620 ! proc append base=usb.MYTABLE (bulkload=yes) data=work.have; ERROR: CLI prepare error: ERROR: 'USER-NAME.MYTABLE error ^ found "-" (at char 0) expecting a keyword
@jmspy - Looks similar to this SAS note: https://support.sas.com/kb/58/291.html
I suspect SAS Tech Support is your best bet.
A member of my company was able to help fix the issue for me
schema='"MY-USER-NAME"'
I've only gotten it to work by hard coding the username and surrounding the double-quoted username with single quotes.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: