topic Re: Is there a limit to read number of records from xlsx file into SAS? in SAS Programming

Is there a limit to read number of records from xlsx file into SAS?

Stalk — Sat, 12 Dec 2020 02:53:45 GMT

My xlsx file has 103465 records when I use the following code or import wizard it only importing 96508 records. What is te best menthod to import all the records into SAS to process?

libname tf xlsx "c:\temp\test.xlsx";

data temp;

tf.sheet1;

run;

Thank you

Re: Is there a limit to read number of records from xlsx file into SAS?

Kurt_Bremser — Sat, 12 Dec 2020 06:46:23 GMT

Save the spreadsheet to a csv file, inspect that with a text editor to verify its contents, then read it with a data step.

Re: Is there a limit to read number of records from xlsx file into SAS?

Kurt_Bremser — Sat, 12 Dec 2020 08:09:30 GMT

Could it be that "sheet1" is not a sheetname, but a named range?

Re: Is there a limit to read number of records from xlsx file into SAS?

jporterf — Sun, 13 Dec 2020 02:32:48 GMT

Hello! I'm a student who recently learned SAS so bear with me.

There shouldn't be a limit for the number of records. Is there more than 1 sheet in the file? If there are and the rest of the records are on another sheet you need to specify each sheet. For example, since you're importing an Excel file you could try and use PROC IMPORT and then use this statement: SHEET = "Sheet1"; (or whatever the title of the sheets are). You would have to do a separate PROC IMPORT for each sheet though. Here's a link that provides more detail: https://communities.sas.com/t5/SAS-Procedures/PROC-IMPORT-multiple-sheets/td-p/149597

Hope that helps!

Re: Is there a limit to read number of records from xlsx file into SAS?

Stalk — Sun, 13 Dec 2020 18:20:52 GMT

I only have one sheet and the sheetname is Sheet1. Saved the xlsx file as CSV still not able to import all the records. I have about 100 variables and 103K records not sure if I want to write a datastep.

Re: Is there a limit to read number of records from xlsx file into SAS?

Kurt_Bremser — Sun, 13 Dec 2020 19:01:45 GMT

Please open the CSV file with a text editor, scroll down to the end, and look at the line number.

Re: Is there a limit to read number of records from xlsx file into SAS?

andreas_lds — Sun, 13 Dec 2020 19:17:53 GMT

Can you post the log, please.

Re: Is there a limit to read number of records from xlsx file into SAS?

SASKiwi — Sun, 13 Dec 2020 19:53:15 GMT

@Stalk - You can use PROC IMPORT with the DBMS = CSV option to both import your CSV and also to generate DATA step code automatically. You may need to use - OPTIONS SOURCE; - to make the DATA step code visible in your SAS log.