Excel data file with proc import hangs up without importing

[dkcundiffMD]

Trying to import a 9.6 MB Excel file with a SAS Excel import snippet leads to hanging up and never completing the import. It got as far as reporting that I successfully created a data set. The same thing happened on my desktop computer as on my laptop with SAS Studio. 

Code:

/** Import an XLSX file. **/

PROC IMPORT DATAFILE="/folders/myfolders/GBDWtedDataDKC2017.xlsx"
OUT=WORK.GBDWtedDataDKC2017
DBMS=XLSX
REPLACE;
RUN;

/** Print the results. **/

PROC PRINT DATA=WORK.GBDWtedDataDKC2017; RUN;

The log screenshot is attached. 

Thanks. 

David Cundiff

[Kurt_Bremser]

Please copy/paste the log text into a window opened with the {i} button. No need for making screenshots of logs, pack those into contaiber files, and attach them. Unnecessary work, and on top the picture in the pdf does not display right (at least on the tablet I'm on).

[dkcundiffMD]

I can't copy and paste the log because the submission of the code hangs up and the log never finishes. I can only screenshot it. 

David Cundiff

[VDD]

@dkcundiffMD the pdf screen shot you provided doesn't correspond to the problem you have noted.

The screen shot indicates that the process imported 7886 records into the GBDWtedDataDKC2017 SAS dataset.  

[dkcundiffMD]

While the code was running, the log was recording that the data file was being imported. However, the code ran for minutes and never finished. I had to terminate the session. I had changed the file from Excel xls to xlsx. Could that have been the problem? 

[Tom]

If all you did was rename the file then it would cause trouble since the format would then be different than what the extension said it was.  Similarly if you tried to read an XLSX file as if it was an XLS file that would not work. 

But if you opened it in Excel and saved it using a different format then that should work.  I guess it is possible that a file in one format that PROC IMPORT has trouble with might work after it is converted by Excel to a different format.

[dkcundiffMD]

I broke the Excel file into less than 1 MB and it would import. At > 3 MB it was hung up. So I have to break my files into < 1 MB sizes? How to do that best? 

Thanks. 

David Cundiff

[Kurt_Bremser]

I guess I can tell you what happens. The culprit is not the import, but the proc print that will print ~ 8000 * 200 = 1,600,000 values, and that overloads your browser.

If you just want to do a quick visual check, add a obs=10 dataset option in the proc print. But inspecting the dataset in the viewtable tab is much better, anyway.

[ballardw · Accepted solution]

Do you really want to read 7886 rows of data with 209 variables?

How long did you wait after running the proc print? The system has to produce tabular output and that can take a lot of resources sometimes.

If you want to know about your actual data contents then you might consider running Proc Contents (not Print) do describe the data.

Perhaps run Proc Freq for selected variables.

[dkcundiffMD]

When I used proc contents instead of proc print, the code imported the data successfully. 

Thanks to you all. 

David Cundiff

[ballardw]

---

@dkcundiffMD wrote:

When I used proc contents instead of proc print, the code imported the data successfully. 

Thanks to you all. 

David Cundiff

---

The log showed that the import was successful. The issue is the amount of resources Proc Print takes to create large tables.

Once upon a time all of our SAS output was basically sent to printers (in effect). Many schools would have a limit on how many pages a student job would be allowed to print because the potential of creating mounds of paper output would be costly.

I made a dummy data set of 8000 records with 201 numeric variables and printed the output. My system used 1:15 (one minute 15 seconds) of CPU time (which is not the same as "real time" which runs a bit longer) and the resulting HTML file was roughly 52Mbyte due to the all of the style content. Yours might vary based on the ODS style you have set for default. Placing all of the color, font and cell boundaries takes resources for the entire object to be created in memory before displaying in the Results Window.

Other procedures that produce output such as Proc Freq can have similar apparent "hangs" as the tables are prepared for display.