Date: Tue, 29 Apr 1997 09:41:38 -0400
Reply-To: "Carl H. Ostermann" <osterman@POBOX.UPENN.EDU>
Sender: "SAS(r) Discussion" <SAS-L@UGA.CC.UGA.EDU>
From: "Carl H. Ostermann" <osterman@POBOX.UPENN.EDU>
Organization: University of Pennsylvania
Subject: Re: Reading excel files with DDE
If I understand the question, I save the Excel file as a tab delimited file.
Then in SAS I do the following:
INFILE 'Mcintosh HD:carl:test.out' EXPANDTABS;
I have also used the DLM statement for comma delimited files.
In article <email@example.com>, Pamela G Tucker
> In article <firstname.lastname@example.org>,
> email@example.com (stephen henri devoto) wrote:
> >My wife is just beginning to use SAS, and I am occasionally trying to help
> >her with the computer part of things. I have a very similar problem, but
> >with the Mac OS (both Excel and SAS). I cannot get SAS to read in Excel
> >data under any circumstances. No matter how I save the data in Excel (tab
> >delimited, etc), SAS can't seem to recognize where the end of one row of
> >data is, and thus treats the entire file as one row of data. I would be
> >very grateful for any advice on this.
> >In article <firstname.lastname@example.org>, email@example.com (John
> >Downing) wrote:
> >> Hello,
> >> In windows 95 I've been trying to read an excel file with some
> >> variation of the following command:
> >> filename mydata dde 'clipboard';
> >> or where I actually specify the DDE triplet after getting it from the
> >> options menu:
> >> filename mydata dde 'excel|c:\[mysheet.xls]sheet1!r1c1:r2c2';
> >> Can anyone help a beginner who wants to read an excel file directly
> >> into sas?
> >Stephen Henri Devoto
> I do not know if this works with any operating
> system other than windows 3.1.
> Here is a way to take an excel data file
> and end up with a sas data file. First save
> the excel file (call it excel.txt) as a space
> delimited text file. Get out of excel. Go into
> Word, or some other text editor, and open (get)
> the file excel.txt from the excel directory.
> Edit the file so that it contains only data
> (no headers) in columns that are all separated
> by exactly one space. Each colum should line up
> nicely. That is, each value in a particular column
> should all take up the same number of spaces
> (you may have to add a zero, +, - ect).
> Save as a text file called edited.txt.
> The sas program below reads in tree variables.
> Var1 take up 2 space, Var2 takes up 3, and Var3 takes 5.
> If it does not work the first time try looking at the
> sas log or/and the edited text for field length problems.
> Hope this helps.
> filename raw 'c:\winword\edited.txt' ;
> data exceldat ;
> infile raw ;
> input Var1 1-2 Var2 4-6 Var3 8-12 ;
> proc print;