Proc SQL with space in the column name

Question

How I can use column with space in the name ('library name') in PROC SQL in SAS?

proc sql outobs=10;
    select *
    from sashelp.vtable 
    where library name = xxx
    ;
run;

I tried:

proc sql outobs=10;
    select *
    from sashelp.vtable 
    where 'Libname'n = test_lin;
quit;

proc sql outobs=10;
    select *
    from sashelp.vtable 
    where 'library name'n = test_lin;
quit;

proc sql outobs=10;
    select *
    from sashelp.vtable 
    where libname = test_lin;
quit;

ERROR: The following columns were not found in the contributing tables: test_lin.

sashelp.vtable

Variable Name: libname

Variable Label: Library Name

Try `where libname = test_lin`: you do not need SAS Name Literals for `libname`. `'Libname'n` makes it case-sensitive. — Y.B., Jun 27 '16 at 09:17
Looks like `test_lin` is a variable. You put it here like just another column name. — Y.B., Jun 27 '16 at 09:27

score 11 · Accepted Answer · answered Jun 27 '16 at 08:35

11

According to documentation - SAS Name Literals:

proc sql outobs=10;
    select *
    from sashelp.vtable 
    where 'library name'n = xxx
    ;
run;

A SAS name literal is a name token that is expressed as a string within quotation marks, followed by the upper- or lowercase letter n. ... You can use a name literal only for variables, statement labels, and DBMS column and table names.

answered Jun 27 '16 at 08:35

Y.B.

3,526
14
24

I get - ERROR: The following columns were not found in the contributing tables: BF_REF, library name. – PNPTestovir Jun 27 '16 at 08:47
1

Well, do you have those columns in `sashelp.vtable`? The error itself indicates that SAS is looking for the column `library name` now. – Y.B. Jun 27 '16 at 08:54
1

@PNPTestovir: just checked sashelp.vtable in my environment (sas9.3, sas base), got only `libname` as column name but not `library name`, are you sure the column exists in you environment? If yes then maybe this could be another question... – kl78 Jun 27 '16 at 08:57
1

Please try it observing the case: `'Library Name'n`. – Y.B. Jun 27 '16 at 09:03
The same for 'Library Name'n. – PNPTestovir Jun 27 '16 at 09:09
2

@pnptestovir: I guess library name is only label name, not variable name, try libname instead. Or do a proc contents on vtable to verify.EDIT: Checked it with proc contents, library name is only label, to access it you must use variable name which is libname – kl78 Jun 27 '16 at 09:10
1

Try `describe view sashelp.vtable` to see the actual column names. 'Library Name' can be a display alias for the actual `libname` column. – Y.B. Jun 27 '16 at 09:12
@kl78 Yeah, you're right! Variable: libname, label: library name. – PNPTestovir Jun 27 '16 at 09:18

score 1 · Answer 2 · answered Jun 27 '16 at 08:35

1

You need to set DQUOTE=ANSI (default is DQUOTE=SAS) and then you'll be able to use quotation marks for names: "library name".

You can find details here: http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#a002473669.htm

answered Jun 27 '16 at 08:35

Andrew Lygin

6,077
1
32
37

score 0 · Answer 3 · answered Jun 27 '16 at 09:02

0

try to use `` mark... or try to use the bracket []... so it will be like library name or [library name];

select *
from sashelp.vtable 
where `library name` = xxx or [library name] = xxx;

answered Jun 27 '16 at 09:02

J. Zend

88
7

It doesn't work. Both - ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant, a missing value, (, +, -, BTRIM, CALCULATED, CASE, EXISTS, INPUT, NOT, PUT, SUBSTRING, TRANSLATE, USER, ^, ~. – PNPTestovir Jun 27 '16 at 09:04
1

This is MySQL specific. – TylerH Mar 01 '18 at 21:51

Proc SQL with space in the column name

3 Answers3