LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (July 2004, week 4)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
Date:         Tue, 27 Jul 2004 09:15:46 -0400
Reply-To:     Nathaniel_Wooding@DOM.COM
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Nat Wooding <Nathaniel_Wooding@DOM.COM>
Subject:      Re: Problem reading Excel data containing spaces w/ dde
Comments: To: Mark Biek <markb@STEVENSONCOMPANY.COM>
Content-type: text/plain; charset=US-ASCII

Mark

It tells SAS to read a not use blanks as variable separators. There are several input modifiers that can be used to deal with character input. I copied the following from the online docs

Modified List Input

List input is more versatile when you use format modifiers. The format modifiers are as follows:

|-----------------+----------------------------------------------------------------------------------------------| | | | | Format | Purpose | | Modifier | | | | | |-----------------+----------------------------------------------------------------------------------------------| | | | | & | reads character values that contain embedded blanks. | | | | |-----------------+----------------------------------------------------------------------------------------------| | | | | : | reads data values that need the additional instructions that informats can provide but that | | | are not aligned in columns. ** | | | | |-----------------+----------------------------------------------------------------------------------------------| | | | | ~ | reads delimiters within quoted character values as characters and retains the quotation | | | marks. | | | | |-----------------+----------------------------------------------------------------------------------------------| | | **Use | | formatted | | input and | | pointer | | controls to | | quickly read | | data values | | aligned in | | columns. | | | | ----------------------------------------------------------------------------------------------|

For example, use the : modifier with an informat to read character values that are longer than 8 bytes or numeric values that contain nonstandard values.

Because list input interprets a blank as a delimiter, use modified list input to read values that contain blanks. The & modifier reads character values that contain single embedded blanks. However, the data values must be separated by two or more blanks. To read values that contain leading, trailing, or embedded blanks with list input, use the DELIMITER= option in the INFILE statement to specify another character as the delimiter. See Reading Delimited Data with Modified List Input. If your input data use blanks as delimiters and they contain leading, trailing, or embedded blanks, you may need to use either column input or formatted input. If quotation marks surround the delimited values, you can use list input with the DSD option in the INFILE statement.

Nat

Mark Biek <markb@STEVENSONC To: SAS-L@LISTSERV.UGA.EDU OMPANY.COM> cc: Sent by: "SAS(r) Subject: Re: Problem reading Excel data containing spaces w/ dde Discussion" <SAS-L@LISTSERV.U GA.EDU>

07/27/04 09:01 AM Please respond to Mark Biek

Nat- Thanks, that did the trick. Can you explain what the & is doing?

Mark

Nat Wooding wrote:

> Mark > > Try adding an & after the variable desc . > > Nat Wooding > > data a; > length desc $255.; > input Code Recode Desc &; > cards; > 1 2 Value1 > 2 2 Value1 > 3 6 Value Other > 4 6 Value Other > 5 4 Value2 > proc print;run; > > > > Mark Biek > <markb@STEVENSONC To: SAS-L@LISTSERV.UGA.EDU > OMPANY.COM> cc: > Sent by: "SAS(r) Subject: Problem reading Excel data containing spaces w/ dde > Discussion" > <SAS-L@LISTSERV.U > GA.EDU> > > > 07/27/04 08:47 AM > Please respond to > Mark Biek > > > > > > > I have some data in Excel that looks like this: > > Code Recode Desc > 1 2 Value1 > 2 2 Value1 > 3 6 Value Other > 4 6 Value Other > 5 4 Value2 > > Which I'm trying to read in using the following code: > > filename q2 dde "excel|q2!r2c2:r42c4"; > data rc_q2; > length desc $255.; > infile q2; > > input code recode desc; > run; > > It works except the Desc field is truncated whenever there is a space > so Code 3 ends up being: > > 3 6 Value > instead of > 3 6 Value Other > > What do I need to do to get the whole field? > > Thanks, > Mark > > -- > Mark Biek > The Stevenson Company > 8700 Westport Rd. Ste. 200 > Louisville, KY 40242-3100 > (502) 429-9060 ext 251 >

-- Mark Biek The Stevenson Company 8700 Westport Rd. Ste. 200 Louisville, KY 40242-3100 (502) 429-9060 ext 251


Back to: Top of message | Previous page | Main SAS-L page