SAS EG 7.1 import data wizard settings

[Mitreto]

Dear colleagues,

I'm importing Excel file[.xlsx] via the import data wizard. One of the columns contains missing data, some numeric values [right aligned in Excel], some text values [left aligned in Excel]

When I reach the stage where I can amend the attributes of the columns the suggestion from SAS is that this should be a numeric column and the Type is slightly greyed out. I wasn't able to change the type directly from the row, but when I selected it  I succeeded via Modify in the bottom right corner.

I have set this to String and with enough length to accommodate all text data.

I think as a result SAS imports that variable as numeric and then converts it to text. The text in not imported and all the missing data has . [dot], which is left aligned.

I should probably mention that the file is pass protected, but I have it opened.

Can I just ask, why the attributes are greyed out and why when specifically instructed to import string fails to do so...

I do apologise but it is clients data, so I cannot share any 

Thank you

Kind regards

Marin  

[SASKiwi]

Saving the spreadsheet as a CSV and importing that will make controlling of data types a lot easier.

[Mitreto]

Thank you for your kind suggestion, but unfortunately this is not an option. The process is designed to receive Excel data.

I'm actually far more interested in the fact that Type in the wizard is greyed out and it cannot be changed, when I fix that - this will fix the issue...

Thank you

Marin

[SASKiwi]

Not sure I follow your logic here. Once your data is imported it is SAS data, not Excel or CSV data - where it came from doesn't matter. Excel is such a pain in the a** for not being able to lock in column data types. That is why I suggested trying a CSV version. Then you can write a SAS program where you can enforce data types.

If you stick with Excel then you could try the "Text to Column" feature in Excel or add a dummy row of data putting say 0 where you want a column to be numeric, before you import.

[Mitreto]

Well, I do agree that once imported SAS data is SAS data [I also agree that Excel is challenging at times]. My questions now are for the procedure of importing via proc import wizard, before the data becomes SAS data. They are more generic - how SAS works rather than the specific file [unless this whole thing is a glitch].

If I'm to request a *.csv to be provided then I'll have to change a business process that was already agreed, which is not what we can do right now.

The screen below is for a random file, as I cannot use the one from the client. The text in the black rectangle for the procedure in question is greyed out into Numeric and 8 [the same way as Source Name below]. When I try to click in either of the fields I am not given the option of the drop-down menus in order to change the type and informat.  

I can do that using the Modify option at the bottom and I do that changing the type to String and length, informats / formats enough to accommodate the text inside the Excel table

 

As I said in the original post the data in that column was of mixed type - predominantly missing, but with some numbers [right aligned] and alphanumeric values [left aligned]. When the import procedure finishes the alphanumeric values are not imported and the column looks like:

it looks like the column was imported as numeric and then converted to text and this will explain the fact the the alphanumeric values were not imported and there are left aligned dots.

So my questions are:

1. What caused the type and informat to be greyed out? Is it just a glitch? Obviously if it is a random event I'll have to think about potential replacement of this process as I rely of all data to be imported correctly.
2. Even if we assume that the first N records were either missing of numeric and that made SAS to suggest numeric format for this column why this was not imported correctly when the type / formats were changed within the procedure itself.

Thank you for your cooperation   

[SASKiwi]

I've tried the Import Wizard on a randomly-chosen XLSX workbook and all columns are modifiable. If I understand you correctly only the client's workbook has this greyed-out problem and not others. So I guess the question is what is the cause? Is the password-protecting causing this? Can you try a "File Save As" on this workbook to drop the password protection and see if this changes the Import Wizard behaviour?