topic Re: Excel data file with proc import hangs up without importing in New SAS User

Excel data file with proc import hangs up without importing

dkcundiffMD — Sun, 15 Dec 2019 04:32:39 GMT

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

Re: Excel data file with proc import hangs up without importing

Kurt_Bremser — Sun, 15 Dec 2019 08:15:31 GMT

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).

Re: Excel data file with proc import hangs up without importing

dkcundiffMD — Sun, 15 Dec 2019 14:59:24 GMT

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

Re: Excel data file with proc import hangs up without importing

VDD — Sun, 15 Dec 2019 15:11:05 GMT

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

Re: Excel data file with proc import hangs up without importing

dkcundiffMD — Mon, 16 Dec 2019 00:22:33 GMT

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?

Re: Excel data file with proc import hangs up without importing

Tom — Mon, 16 Dec 2019 00:53:13 GMT

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.

Re: Excel data file with proc import hangs up without importing

dkcundiffMD — Mon, 16 Dec 2019 01:14:13 GMT

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

Re: Excel data file with proc import hangs up without importing

Kurt_Bremser — Mon, 16 Dec 2019 08:22:27 GMT

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.

Re: Excel data file with proc import hangs up without importing

ballardw — Mon, 16 Dec 2019 17:51:05 GMT

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.

Re: Excel data file with proc import hangs up without importing

dkcundiffMD — Mon, 16 Dec 2019 20:42:03 GMT

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

Thanks to you all.

David Cundiff

Re: Excel data file with proc import hangs up without importing

ballardw — Mon, 16 Dec 2019 21:09:38 GMT

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