indsname= and n-literal

[xxformat_com]

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;

[Reeza]

Don't you need 44?

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.

[xxformat_com]

yes, 44 is what I should have written. but the issue remains.

[Tom · Accepted solution]

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