DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Contributor
Posts: 32
Accepted Solution

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

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


Accepted Solutions
Solution
3 weeks ago
Valued Guide
Posts: 558

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

[ Edited ]

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.

Thanks,
Suryakiran

View solution in original post


All Replies
Solution
3 weeks ago
Valued Guide
Posts: 558

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

[ Edited ]

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.

Thanks,
Suryakiran
Contributor
Posts: 32

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

Posted in reply to SuryaKiran
Thank you very much Suryakiran, does GUESSINGROWS work with XLSX?
Valued Guide
Posts: 558

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

[ Edited ]

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.

image.png

Thanks,
Suryakiran
Respected Advisor
Posts: 4,665

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

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

Super User
Posts: 9,868

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

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 10,681

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

Try this :

 

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

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 221 views
  • 7 likes
  • 5 in conversation