proc import a column with date values and numeric

[novice_sasuser]

I have a scenario where we receive an input file (xlsx) whose columns and formats are unknown but for the first 10.

The sender can add any number of columns and have no format constraint.

Currently trying to load a file with Col1 - Col 23.

I have an issue on Column 17. It contains both a date and a decimal value.

So SAS is reading this as num8.

Is there an option or method where I can import all columns effective Col11 as text and print them as is.?

Please let me know if I can provide any further information,

Thanks A ton.

[PaigeMiller]

Many people here will not download attachments, particularly Microsoft Office documents, which can be a security risk. Please provide a portion of the data as working SAS data step code (examples and instructions) and not any other way. It is fine if you just show us column 17.

[Tom · Accepted solution]

No.

You could ask them to send a text file by saving the XLSX to a CSV (or other delimited text file).

But I am not sure how reading the mixed up column as text is going to work any better than the numeric value you say you are getting.  How are you going to distinguish the dates from the other values?  Why not just do the same thing with the numbers?

[ballardw]

I looked at your docx and see "Additonal Variable 1". What the heck does that even mean?

If someone tells me there is to be an "additional" variable, I would expect to have a variable name and a description of where the values would come from. You have a list of values in that column.

If these different rows (you provide a very limited picture so hard to tell) actually all belong in the same record then perhaps if the data is provided as a text file you can read it the three row values into separate variables that would allow mixing text, date values and other numeric values. Maybe.

Or maybe your file needs to be restructured. Without a moderately complete example of the file contents it is very hard to tell if that is desirable or possible.

You are learning why spreadsheets are quite often not good ways to interchange data. That may be the main "learning" point at this time. Spreadsheets do not enforce any "variable" or "column" properties that a proper data structure does.