BookmarkSubscribeRSS Feed
Quartz | Level 8


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 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?



Quartz | Level 8
It is a .xlsx file.

Super User Tom
Super User

@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?

Super User

@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.

Super User
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.



Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Get the $99 certification deal.jpg



Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 5 in conversation