- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
Did I miss something here?
When the n-literal syntax is used to read an Excel worksheet having a name with 31 characters including blanks, quotations marks and n (n-literal syntax) are added in the temporary variable defined by indsname=. However, this variable has 41 characters by default i.e. 8 for the libname, 32 for the dataset name and 1 for the dot. The final quote and n are truncated if the libref is 8 character long.
But increasing the variable length of the permanent variable storing the temporary variable to 43 characters does not solve the issue.
Is there any workaround e.g. an extra option to use here?
libname testwxyz xlsx "&xxtest./xlsxengine31.xlsx";
data testwxyz.'_23456789 123456789 12345678xyz'n;
set sashelp.class;
run;
libname testwxyz clear;
libname testwxyz xlsx "&xxtest/xlsxengine31.xlsx";
data test;
set testwxyz.'_23456789 123456789 12345678xyz'n (where=(sex='M'))
testwxyz.'_23456789 123456789 12345678xyz'n (where=(sex='F'))
indsname=_tmp;
length chk $43;
chk=_tmp;
run;
libname testwxyz clear;
proc print data=test noobs;
run;
ods select position;
proc contents data=test varnum;
run;
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
You seem to have found a BUG in the INDSNAME= option. It will not produce more than 41 characters no matter what length you define for the variable.
So if this is a serious concern for you then you might need to use some other method to capture the contributing dataset's name. For example create views using shorter names and set the views and then translate the short name back to the original name.
To really be sure there is enough room for a full dataset name you need to use a length of 76.
The maximum length of libref is 8. The period takes 1. Plus 32 for the actual name. 3 for the outer quotes and the letter N. And if you use the sub-optimal choice for which outer quote you might need to double up to all 32 of the characters in the actual name.
Example:
70 data _null_; 71 length name $32 nliteral $80; 72 name=repeat("'",31); 73 nliteral=cats('L2345678.',quote(name,"'"),'n'); 74 len = length(nliteral); 75 call symputx('name',nliteral); 76 put len=; 77 run; len=76
Let's try using that goofy name and we can see the truncation to 41 bytes actually happen.
1973 libname L2345678 (WORK); NOTE: Libref L2345678 was successfully assigned as follows: Levels: 1 Engine(1): V9 Physical Name(1): C:\Users\... 1974 1975 data &name ; 1976 x=3; 1977 run; NOTE: The data set L2345678.''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''n has 1 observations and 1 variables. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 1978 1979 data test; 1980 length dsn indsn $80 ; 1981 set &name indsname=indsn; 1982 dsn=indsn; 1983 len=length(dsn); 1984 put x= len= dsn= ; 1985 run; x=3 len=41 dsn=L2345678.'''''''''''''''''''''''''''''''' NOTE: There were 1 observations read from the data set L2345678.''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''n. NOTE: The data set WORK.TEST has 1 observations and 3 variables. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
2 quotes
1 period
1 n
32 characters
8 libname
=44 characters. I'd set it to 50 just to test it anyways.
Move the length before the SET statement and apply it to both the _tmp and chk variables.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
You seem to have found a BUG in the INDSNAME= option. It will not produce more than 41 characters no matter what length you define for the variable.
So if this is a serious concern for you then you might need to use some other method to capture the contributing dataset's name. For example create views using shorter names and set the views and then translate the short name back to the original name.
To really be sure there is enough room for a full dataset name you need to use a length of 76.
The maximum length of libref is 8. The period takes 1. Plus 32 for the actual name. 3 for the outer quotes and the letter N. And if you use the sub-optimal choice for which outer quote you might need to double up to all 32 of the characters in the actual name.
Example:
70 data _null_; 71 length name $32 nliteral $80; 72 name=repeat("'",31); 73 nliteral=cats('L2345678.',quote(name,"'"),'n'); 74 len = length(nliteral); 75 call symputx('name',nliteral); 76 put len=; 77 run; len=76
Let's try using that goofy name and we can see the truncation to 41 bytes actually happen.
1973 libname L2345678 (WORK); NOTE: Libref L2345678 was successfully assigned as follows: Levels: 1 Engine(1): V9 Physical Name(1): C:\Users\... 1974 1975 data &name ; 1976 x=3; 1977 run; NOTE: The data set L2345678.''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''n has 1 observations and 1 variables. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 1978 1979 data test; 1980 length dsn indsn $80 ; 1981 set &name indsname=indsn; 1982 dsn=indsn; 1983 len=length(dsn); 1984 put x= len= dsn= ; 1985 run; x=3 len=41 dsn=L2345678.'''''''''''''''''''''''''''''''' NOTE: There were 1 observations read from the data set L2345678.''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''n. NOTE: The data set WORK.TEST has 1 observations and 3 variables. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds