PROC Import and Modifying INFORMATS AND FORMATS

[edasdfasdfasdfa]

Hello,

I want to modify the informat for a particular variable as an income variable is not showing properly.  I was told this can be done through the log..ie modify it in the log. But I don't see the informats on there. Is there an option or something that it needed to show that?

Thanks

[Kurt_Bremser]

What kind of file are you reading? Only for text-based files will proc import create a data step, which you can copy from the log and modify to your needs.

[edasdfasdfasdfa]

It is a .xlsx file.

[Tom]

---

@edasdfasdfasdfa wrote:
It is a .xlsx file.

---

If you are reading from an XLSX file then SAS will define the variables based on how the cells in the spreadsheet columns are defined. If the cells are of mixed type then the variable will be character, since you can always convert a number to a character representation but you cannot always do the opposite.  

Is there some particular problem you are having?

[ballardw]

---

@edasdfasdfasdfa wrote:
It is a .xlsx file.

---

One approach:

Export to a CSV file and then import that CSV. Then you will have the data step code in the LOG to read a csv file. You may want to use the GUESSINGROWS option to specify a large number of rows to examine before SAS sets the properties for the variables. Otherwise only 20 or so rows are used which is a frequent problem.

That code could be copied, pasted into the editor and edit the Informat and format statements.

I convert dozens of xlsx to csv files monthly to have control while reading the files because I can't trust my data sources not to change value codes, change column headings or change column order.

[Reeza]

Basically you can't control the types and formats when reading from an Excel file, because Excel doesn't enforce any types. So you either clean them up afterward, convert your file to CSV and then use the IMPORT + modification option you were trying initially.