Hello everyone! This is my very first post (after reading actively on the forum for a while now) since I couldn't find a similar issue on this board already. I am testing a piece of code on different servers, one running 9.3 and the other one 9.4, and there is a different behavior when importing XLSX files, in particular when dealing with character variables. In short, the import procedure in SAS 9.3 will assign a minimum length of 3 times the observed length to a variable containing strings, whereas the SAS 9.4 version only uses a minimum length needed to fit the observed values into the variable. I attached a minimum example of the code that reproduces this behavior on the SAS servers I am running on. proc import out = work.long_id datafile = "PATH_TO_THE_EXCEL\example_long_short_id.xlsx"
DBMS=xlsx REPLACE;
SHEET="Long ID";
GETNAMES=YES;
run;
proc import out = work.short_id datafile = "PATH_TO_THE_EXCEL\example_long_short_id.xlsx"
DBMS=xlsx REPLACE;
SHEET="Short ID";
GETNAMES=YES;
run;
/* Get the information about the length of the variables */
title "Observations with short id";
proc contents data = work.short_id;
run;
title "Observations with long ID";
proc contents data = work.long_id;
run;
/* Concatenate data */
data work.concatenate;
set work.short_id work.long_id;
run;
title "Concatenated table (short id table comes first, long id is truncated under SAS 9.4)";
proc contents data = work.concatenate;
run;
proc print data = work.concatenate;
run; Screenshots of the Excel input are attached, they are very simple. First Excel Sheet Second Excel Sheet The "short id" Excel-Sheet has variables with length of 9 and under SAS 9.3 and 9.4 it is read in with length 27 and 9, respectively. For the "long id" the length is 13 and it is imported with length of 39 (SAS 9.3) or 13 (SAS 9.4). As stated above, the SAS 9.3 server seems to choose three times the length of the maximum length it observed in the first X rows, whereas SAS 9.4 chooses only the maximum length. Alhtough I can reproduce and identify this difference in behaviour, I don't know how to adjust this behaviour in the settings, because the behavior of SAS 9.3 is the one needed. This brings me to my question(s): Which settings of the SAS 9.4 server need to be adjusted so that the length assigned to a column when importing an xlsx is three times the length of the variables observed? Where can I find the documentation that the settings were changed when switching from SAS 9.3 to SAS 9.4? I attached some screenshots of the output as well, please excuse the German output of the server 😉 It's the proc contents output of the sheet with long IDs. You can see the different lengths assigned by SAS when running on different servers. Proc Contents Output under SAS 9.3 Proc Contents Output under SAS 9.4 Thanks a lot, I hope I adhered to the rules of posting and look forward to your suggestions. Cheers, icecube
... View more