Date:         Mon, 29 Mar 1999 14:30:03 -0500
Reply-To:     Howard Schreier <Howard_Schreier@ITA.DOC.GOV>
Sender:       "SAS(r) Discussion" <SAS-L@UGA.CC.UGA.EDU>
From:         Howard Schreier <Howard_Schreier@ITA.DOC.GOV>
Subject:      Problem 1900 in Excel:no solution?
Content-Type: text/plain; charset=US-ASCII

I agree with John's suggestion.
 
But if it is an ongoing requirement, or there are a large number of Excel files to process, and if Excel is available, one might want to use DDE instead of PROC ACCESS. This would permit a one-step solution (Excel to SAS with no intermediate file).
 
DDE is a low-level tool, and as a consequence it does not assume that it is processing a "proper" data base table. In this case, it would allow the rows of the Excel file to be passed to the DATA step's input buffer one at a time. The bad news is that one then has to code the INPUT statement(s) to create SAS variables. The good news is that one has the flexibility of the INPUT statement to handle anomalies and special cases and so forth.
 
>On Fri, 26 Mar 1999 18:15:21 +0000,  John Whittington wrote:
 
>At 09:53 26/03/99 -0500, Victor Kamensky wrote (in part):
>
>>I concluded that this problem has no solution.
>>Problem:
>>We have an Excel file.
>>There is a column with dates(fully typed with 4-digit years).
>>Some dates are in 19 century(not very much, not consequent,
>>in the middle of the file).
>>Direct attempt to convert it to  SAS by PROC ACCESS
>>converts 19 century to missing values.
>>By playing with Excel I realized that EXCEL just cannot consider 19
>>century dates as dates, only as  characters.
>>SAS considers this field as numeric, and
>>characters are missing.
>
>Victor, assuming you have Excel, and particularly if this is just a
>'one-off' exercise, why not forget about PROC ACCESS and instead simply
>output the data as a .CSV file from Excel, then read it into SAS using a
>DATA step - whereupon you will have full control over how SAS 'regards' each
>variable?  You can then read in the offending field(s) as a character
>variable, then convert it to a SAS date variable (with an INPUT function)
>within SAS.