topic Re: Import of Excel - Excel Label Character Length in New SAS User

Import of Excel - Excel Label Character Length

emrahbagci82 — Mon, 29 Apr 2019 14:22:10 GMT

Hello Dear SAS users,

I have found out something about excel import.and wanted to confirm it.

I have data excel including customer numbers and have 300.000 lines. When i name that excel as lets say TEST.xlsx (which is 4 characters), i be able to import, but when i name as lets say "CUSTNUMBERLIST.xlsx" (wihch is 14 charcters) i am not able to import.

Data is the same, only difference is the label character length. So is there a character length limit to succesfully import? Icould find in any document.

Best regards.

Re: Import of Excel - Excel Label Character Length

Jagadishkatam — Mon, 29 Apr 2019 13:06:00 GMT

Could you please let share the code you used.

Re: Import of Excel - Excel Label Character Length

Tom — Mon, 29 Apr 2019 13:07:50 GMT

Are you talking about the column headers in the Excel spreadsheet that will be converted into the variable NAME in SAS?

Or are you talking about the sheetname in Excel workbook that will be converted into the member name (dataset name)?

Make sure that you have not set the VALIDVARNAME option to V6 as that only supports 8 characters for variable names.

Re: Import of Excel - Excel Label Character Length

emrahbagci82 — Mon, 29 Apr 2019 13:22:43 GMT

Dear Tom,

I am not talking about both of them. My point is about the the name of the document. The excel and the data are the same, i just change the name of the document. I mean, like "CUST.xls"

Re: Import of Excel - Excel Label Character Length

Tom — Mon, 29 Apr 2019 13:42:55 GMT

Please show what code you used. If you used point and click method then describe what you pointed at and clicked on.

Re: Import of Excel - Excel Label Character Length

emrahbagci82 — Mon, 29 Apr 2019 14:15:28 GMT

I am sending ss of the steps i follow. one for with result and one for no result.

I dont use codes, just choose from lists, click and go.

Re: Import of Excel - Excel Label Character Length

Tom — Mon, 29 Apr 2019 14:24:49 GMT

Looks like you are using Enterprise Guide and it is converting the spreadsheet to a text file, uploading the text file, and running a data step to read it. Your screen shot does not actually show any errors. In fact it seems to indicate that it worked.

Did you get any errors?

If so copy the TEXT from the log and paste it into the forum using the Insert Code icon (looks like {i} ).

Re: Import of Excel - Excel Label Character Length

emrahbagci82 — Mon, 29 Apr 2019 14:30:09 GMT

it doesnt Show any errors, in the 4th ss of no result Word, below you can see, there are no errors, but 4 notes.

as i said, only difference is the name of the document. when 4 characters like MUST.xlsx, its imported, but when named as CUSTMUSTLIST.xlsx, its not imported. So i guess its becuase of the character length of documents name but it doesnt make sense.

Re: Import of Excel - Excel Label Character Length

Tom — Mon, 29 Apr 2019 14:39:07 GMT

The notes are clearly showing that it read many lines and wrote many observations.

Try running a PROC CONTENTS on the dataset it generated. Perhaps that will explain why the GUI is not showing you the data?

Re: Import of Excel - Excel Label Character Length

emrahbagci82 — Mon, 29 Apr 2019 14:47:27 GMT

it reads many same lines for sport named version too.

i dont use proc contents, so dont have any idea what you say about proc contens. only thing i know, same data but different results.

Re: Import of Excel - Excel Label Character Length

Tom — Mon, 29 Apr 2019 14:55:13 GMT

@emrahbagci82 wrote:

it reads many same lines for sport named version too.

i dont use proc contents, so dont have any idea what you say about proc contens. only thing i know, same data but different results.

Learn to read your logs. SAS does a good job of showing what it is doing.

There are so many things in a GUI setting like Enterprise Guide that can cause it to appear that data is missing. Perhaps EG does not automatically open large datasets? Are you sure you are trying to open the dataset that the log shows was created? Have you applied some filter in EG that is making it filter out all of the observations? Have you run some other steps after the IMPORT that has modified the data?

You should be able to see what datasets exist using EG menus and push buttons. Do you see that dataset? Does it have variables? Does it have observations?

Did you change the data file format from XLS to XLSX when you changed the filename? Perhaps that is causing the IMPORT step to interpret the columns differently?

Re: Import of Excel - Excel Label Character Length

ballardw — Mon, 29 Apr 2019 14:55:34 GMT

@emrahbagci82 wrote:

it reads many same lines for sport named version too.

i dont use proc contents, so dont have any idea what you say about proc contens. only thing i know, same data but different results.

Proc Contents tells you things about the data such as number of records, variable names, types and formats. As such it is one of the basic diagnostic tools about the contents of data.

Without sharing complete code and log from running both bits of code, or the results of the code or a very precise description we have no way to start to say why you may have "different results". Different how: different numbers of observations, different variables, no output (really important to post log of no output with proc import).

And considering the number of different things that people may have done to an XLSX file, you need to make sure that other than name that the two files are identical before claiming that the "name" length is an issue.

Did you consider changing the name of the "Custnumberlist" to something like "ABCD" and see if that "works" differently? If not try it. Then report back on if the results were any different.

Re: Import of Excel - Excel Label Character Length

emrahbagci82 — Tue, 30 Apr 2019 07:12:03 GMT

Your questions shows me that you didnt read what i wrote before.

I say, I JUST CHANGE THE NAME OF EXCEL, all other things are the same. with CUST. xlsx it shows result, but with CUSTNUMBERLIST.xlsx no result. We dont need any other information i think.

Re: Import of Excel - Excel Label Character Length

emrahbagci82 — Tue, 30 Apr 2019 07:20:38 GMT

i have already talked that i changed the name and tested. deal is that, same data but when the name is "CUST.xlsx" it shows output but when its "CUSTNUMBERLIST.xlsx" there is no output. no other changes inside our outside the excel.

There must be an easy reason for it without seeing the codes, logs or sth other. I mean, lets say when the length of the name of excel is higher then 5 characters, SAS EG cant Show output. Its not making sense, i wait a this type of answer.

Re: Import of Excel - Excel Label Character Length

ballardw — Tue, 30 Apr 2019 22:22:06 GMT

@emrahbagci82 wrote:

i have already talked that i changed the name and tested. deal is that, same data but when the name is "CUST.xlsx" it shows output but when its "CUSTNUMBERLIST.xlsx" there is no output. no other changes inside our outside the excel.

There must be an easy reason for it without seeing the codes, logs or sth other. I mean, lets say when the length of the name of excel is higher then 5 characters, SAS EG cant Show output. Its not making sense, i wait a this type of answer.

I am sorry but without a LOG entry of the actual submitted code I can't quite believe that. I would suspect something in a path or filename reference is more likely to be the culprit. If the total length of the path plus the file name exceeds 256 then you have run into a windows limitation. But you have not shown any actual code that was attempted, i.e. a LOG to see if that might be an issue.

I read xlsx files occasionally with much longer names and even stupid characters like () in the names.