Force PROC IMPORT to import to variables of specific length from XLSX

[markc]

Hello,

I am using PROC IMPORT to read a file DBMS=XLSX but truncation is occurring for some vars.   How can I define the variables which come in to be a specific length?

Any help will be Liked promptly.

Many thanks,

Mark

[SuryaKiran · Accepted solution]

You don't have much control over the file if it's XLSX. The best way is to convert it to CSV and then you can use DATA step with infile and define the data attributes you like or you can use PROC IMPORT with GUESSINGROWS= to tell sas to scan all the observation to define the data type instead of default 20. 

Note: GUESSINGROWS works only for delimited files.

[markc]

Thank you very much Suryakiran, does GUESSINGROWS work with XLSX?

[SuryaKiran]

GUESSINGROWS= will not work for XLSX files. 

You may try this: 

Spoiler

So, open SAS 9.4, and in the command line – in the upper left corner of the screen – type regedit. Then navigate to Products-->BASE-->EFI-->GuessingRows and set the value to the number of rows in the CSV or Excel file you want SAS to scan to find the longest value. I chose 1000, because I knew I wouldn't have more than 1000 rows in an Excel file, but you could increase it up to 2147483647 (or MAX), although SAS documentation recommends not to, as it might adversely affect performance. I saw no performance hit ater I changed it to 1000.

Found this here and not sure if that works.

[Patrick]

@markc

What you describe shouldn't happen with the XLSX engine.

Can you give us an example of the kind of truncation you're observing?

If possible: Can you post an Excel with some sample data with which you experience such truncation so we can test if that's replicable for us or limited to your environment. Also: What SAS version in what OS are you using? 

[Kurt_Bremser]

What I've posted probably 300 times or more here: save to csv from Excel and read with a data step. Excel files are ***** for data transfer.

[Ksharp]

Try this :

libname x xlsx '/folders/myfolders/ntile.xlsx' dbmax_text=32767;
proc copy in=x out=work noclone;
run;