topic Re: Force PROC IMPORT to import to variables of specific length from XLSX in SAS Programming

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

markc — Tue, 22 May 2018 23:52:19 GMT

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

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

SuryaKiran — Wed, 23 May 2018 00:03:13 GMT

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.

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

markc — Wed, 23 May 2018 00:03:07 GMT

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

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

SuryaKiran — Wed, 23 May 2018 00:14:54 GMT

GUESSINGROWS= will not work for XLSX files.

You may try this:

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.

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

Patrick — Wed, 23 May 2018 00:22:57 GMT

@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?

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

Kurt_Bremser — Wed, 23 May 2018 07:58:36 GMT

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.

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

Ksharp — Wed, 23 May 2018 12:53:57 GMT

Try this :

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