Newsgroups: bit.listserv.sas-l
Date:         Mon, 9 Nov 1992 11:54:59 PST
Reply-To:     LAW4%RatesRDS%FAR@CTS27.CS.PGE.COM
From:         LAW4%RatesRDS%FAR@CTS27.CS.PGE.COM
Subject:      re: Conversion of SAS files to spreadsheet files.

Sigurdur R Saemundsson writes:

>I seem to remember that there was a discussion, not so long ago, on
>how to convert SAS files to spreadsheet files. My problem, specifically,
>is to get a PC-SAS file into EXCEL. Can anyone fill me in on this?
>Do I really need some extra software?
----------------------------------------------------------------------------
You need to have SAS for OS/2 or SAS for Windows to move data directly from
a SAS file into an Excel spreadsheet.  Otherwise you need to use PC-SAS to
create an intermediate file that can be read by Excel, such as a .CSV (comma
separated file created in a DATA step) or a .DBF file (created by PROC DBF).

If you have SAS for OS/2 or SAS for Windows, then following is a sequence
of some previous SAS-L messages.  (There is nothing new beyond this point
for those of you who have been following the DDE messages.)
----------------------------------------------------------------------------
>Now that SAS is 'playing' on PCs in Windows, I'd like to see SI recognize
>those of us who've been working there, using the PC databases available,
>by building interfaces to major PC DBMS software.  dBase IV and Paradox
>come to mind.  And how about the existing spreadsheet?  I know SI would
>like us all to use THEIR spreadsheet, but lets be realistic: Lotus 1-2-3,
>Excel, and Quattro Pro are nice products with large installed bases.
-----------------------------------------------------------------------------
>Have you checked out DDE (Dynamic Data Exchange)?  This will let you exchange
>data with Excel and Lotus 123 as well as other products that support DDE.

>Look in:
>1.  SAS Communications, Second Quarter 1991
>2.  SUGI 17 Proceedings, page 117 "Data Exchange Between the SAS System and
>Microsoft Excel"  (I wrote this paper, so it is highly recommended!)  This
>paper was written using SAS for OS/2, but DDE is supposed to work the
>same way under Windows.

>I don't have SAS for Windows yet, so I can't tell you what page to look up in
>the Companion manual, but I'm sure DDE is in there somewhere.  Good luck!

Louise Weiler
----------------------------------------------------------------------------
>And to further the cause:

>Yes INDEEDY, DDE does work with Windows SAS Release 6.08 (BETA),  as we have
>successfully linked EXCEL spreadsheets and SAS data.  OLE (Object Linking and
>Embedding) which is even more powerful, is documented in the BETA manuals as
>well.

>There appears to be plans for a .DBF and .DIF SAS/ACCESS product (not much in
>the HELP library on these yet) complete with a DBLOAD procedure for those of
>you xBASE junkies.

>Intel-Insider,

>Tom Skinner
-----------------------------------------------------------------------------
Jack Shoemaker wrote:

>The SAS manual says that the DDE channel is tab-delimited.  I have found this
>NOT to be the case attempting to read an Excel spreadsheet.  For example:

>EXCEL SPREADSHEET:  (small.xls)
>        A       B       C
>1     Jack  North Haven Red Sox
>2     Dean  Ridgefield  Yankees
>3     Will  Brookfield  Mets

>SAS CODE:

>filename inx dde 'excel|small.xls!r1c1:r3c3';

>data inx;
>infile inx dsd truncover;
>input guy $ town $ team $;
>run;

>proc print data = inx;
>run;

>SAS OUTPUT:

>GUY  TOWN     TEAM
>Jack North    Haven
>Dean Ridgefie Yankees
>Will Brookfie Mets

>Further inspection of the DDE channel using the PUT command with $HEXw. format
>revealed that the channel was blank-delimited.  That's why `Haven' ended up in
>the TEAM field instead of part of the TOWN field.

>I believe that we set WINCHARSET to OEM in our configuartion file - I forget
>and can't check right now.  I did use both the OEM and ANSI modifiers on the
>filename statement, but got the same results.

>Comments?  TIA - Jack
>--
>Jack Shoemaker
>Real Decisions Corporation    Voice: 203-656-1500
>Darien, CT  06820          InterNet: shoe@world.std.com
----------------------------------------------------------------------------
I wrote:

>Try out the following: Put a DELIMITER='09'X option in your INFILE statement
>and a NOTAB option in the FILENAME statement.  Now the SAS code should read:

>       filename inx dde 'excel|small.xls!r1c1:r3c3' notab;

>       data inx;
>       infile inx delimiter='09'x;
>       input guy $ town $ team $;
>       run;

>This ought to work.  You might need to put in a length statement for your
>character varaibles.  Sorry, but I don't know what DSD and TRUNCOVER mean,
>so I've left them out.
---------------------------------------------------------------------------
Then I added:

>I neglected to say why I thought this would work.  By default, when SAS
>receives data from Excel, the tabs that Excel uses to delimit columns are
>converted to blanks.  The NOTAB option in the FILENAME statement tells SAS
>not to convert tabs to blanks.  When the NOTAB option is used, you must then
>tell SAS to recognize that tabs are the delimiter in the INFILE statement.
--------------------------------------------------------------------------
Louise Weiler                           INTERNET: law4@cs.pge.com
Pacific Gas and Electric Company        VOICE:  415/973-1355
Rates Dept. B10B                        FAX:    415/973-7451
P.O. Box 770000
San Francisco CA 94177
---------------------------------------------------------------------------
Comments: Gated by NETNEWS@AUVM.AMERICAN.EDU
Newsgroups: bit.listserv.sas-l