Date: Mon, 17 Jul 2006 21:45:00 -0400
Reply-To: "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
Subject: Re: Reading Excel files with numbers that need to be text
On Sat, 15 Jul 2006 14:32:38 -0700, Sierra Information Services
>The first approach that comes to my mind is for you to convert the
>Excel spreadsheet to a CSV file and then bring it in to SAS via a Data
>Step. That way you can assign informats and also decide which
>variables you want stored in the SAS data set as character vs. numeric.
>You could also use the Z Informat to pad out variables for which you
>want leading zeros. I'm thinking that might be the easiest/fastest way
>for you to do this, at least from what you've indicated in your post.
>Here is a quick and dirty example of how to read a two column CSV file
>in to a SAS data set. Note that I'm using the DSD option to have SAS
>treat as missing any value of a variable in the data set with two
> INFILE "C:\MyDocuments\MYCSV.CSV" DELIMITER=',' DSD;
>INPUT COL1 Z8. COL2 Z6;
>Hope this helps.
>Sierra Information Services
Another possibility is to use DDE. You would have all the DATA step control
which Andrew describes, but without having to create an intermediate file.
About the only difference is that the separators would be tabs rather than
>> Windows XP SAS v 9.1. Reading big Excel files that have some variables that
>> should all be read as character values and 6-digits each. The problem is
>> that my data source puts several of the data items as numbers, and if there
>> are leading zeros, it's not reading them right. SAS documentation says that
>> it scans the entire column and reads the variable in as character if any in
>> the column are -- it's not doing that. Specifically, here's the problem.
>> Say, I have the data items below:
>> What I need SAS to read this as is a six-digit text variable with values:
>> It doesn't. In fact, it doesn't read the 345 at all (doesn't even read it as
>> Is there a way to override the Excel defaults and use my specific informat
>> to read them all in as 6 digit text? I would then add leading zeros to all
>> values that had a length less than 6.