topic Issues while importing excel through SAS EG 6.1 in SAS Enterprise Guide

Issues while importing excel through SAS EG 6.1

Arora_S — Thu, 07 May 2015 22:47:15 GMT

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

Re: Issues while importing excel through SAS EG 6.1

Ksharp — Fri, 08 May 2015 13:54:24 GMT

Try option scandate=no ;

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

Re: Issues while importing excel through SAS EG 6.1

Arora_S — Fri, 08 May 2015 17:47:14 GMT

It's not working in ENTERPRISE GUIDE

Re: Issues while importing excel through SAS EG 6.1

ballardw — Fri, 08 May 2015 19:33:15 GMT

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.