BookmarkSubscribeRSS Feed
edasdfasdfasdfa
Quartz | Level 8

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

5 REPLIES 5
edasdfasdfasdfa
Quartz | Level 8
It is a .xlsx file.

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

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

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

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 5286 views
  • 0 likes
  • 5 in conversation