Desktop productivity for business analysts and programmers

Issues while importing excel through SAS EG 6.1

Reply
Contributor
Posts: 35

Issues while importing excel through SAS EG 6.1

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

Super User
Posts: 9,867

Re: Issues while importing excel through SAS EG 6.1

Try option  scandate=no   ;

or try to save it as CSV file ,then import it again.

Contributor
Posts: 35

Re: Issues while importing excel through SAS EG 6.1

It's not working in ENTERPRISE GUIDE

Super User
Posts: 11,134

Re: Issues while importing excel through SAS EG 6.1

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.

Ask a Question
Discussion stats
  • 3 replies
  • 385 views
  • 0 likes
  • 3 in conversation