BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
markc
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
SuryaKiran
Meteorite | Level 14

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

6 REPLIES 6
SuryaKiran
Meteorite | Level 14

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
markc
Obsidian | Level 7
Thank you very much Suryakiran, does GUESSINGROWS work with XLSX?
SuryaKiran
Meteorite | Level 14

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
Patrick
Opal | Level 21

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

Ksharp
Super User

Try this :

 

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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