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
Saving the spreadsheet as a CSV and importing that will make controlling of data types a lot easier.
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
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.
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:
Thank you for your cooperation
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?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: