I have an excel file (xlsx) with mixed - character and numeric format. While using proc import statement , SAS converts all character dates in sas date format but moves them ahead by 60 years.
I want to read the excel file as such with NO formatting change - basically read and output the data the way it is in excel exactly.
Tried using Mixed = yes (works only in xls and not xlsx). DBDSOPTS = DBTYPE =option is also not working in SAS EG 6.1.
Input data looks like this in excel sheet:
Results
XYZ ABCD
date Number Value Amount(Number)
Jan-09 98363 -
Mar-10 57245 -
Dec-15 6824648 (100) Note: Parentehesis This is a negative number in excel
Need output SAS data set should like this as above:
Results
XYZ ABCD
date Number Value Amount(Number)
Jan-09 98363 -
Mar-10 57245 -
Dec-15 6824648 (100)
Appreciate all help on this .
thanks
Try option scandate=no ;
or try to save it as CSV file ,then import it again.
It's not working in ENTERPRISE GUIDE
I suggest opening the EXCEL file, finding that cell and examining the contents. A displayed value of 1-Jan-15 could be 1/1/2615 when displayed with the Excel display setting using the dd-mmm-yy type of display.
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
Check out this tutorial series to learn how to build your own steps in SAS Studio.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.