Date:         Mon, 12 Jan 2009 17:08:10 -0600
Reply-To:     Joe Matise <snoopy369@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Joe Matise <snoopy369@GMAIL.COM>
Subject:      Re: Read Multiline Cell Data in Excel
Comments: To: Steve James <spj1@cdc.gov>
In-Reply-To:  <52b4dfa8-c903-4b36-9ad8-1f83ed90cb97@f18g2000vbf.googlegroups.com>
Content-Type: text/plain; charset=ISO-8859-1

You mean linefeed inside a single cell ('0a'x, entered in with alt+enter in
excel)?

You should be able to do:

(assuming proc import -> have, with getnames=no and dbms=excel)

data want;
set have;
i=1;
do i = 1 to countc(F1,'0a'x)+1;
 newF1 = scan(F1,i,'0a'x);
 output;
end;
run;


If I have to guess I'd think you are doing a data dictionary -> formats
conversion, which I do with this code:
(values = your cell A1's variable name, type = 'CHAR' or 'NUMERIC', fmtname
= name of format, values_new = the format statement).  It uses PRXCHANGE to
convert the LF to a space so you can create the values statement directly.

/* code */
if not missing(values) then do;
    ***this prxchange replaces the carriage return character found in the
excel file, so it can be used in the format statement;
    if upcase(type) = "CHAR" then values_new = catx("
",'value',cats('$',fmtname))||" "||cats((prxchange('s/=
/="/',-1,prxchange('s/\n/" / ',-1,values))),'";');
    else values_new = catx(" ",'value',fmtname)||" "||cats((prxchange('s/=
/="/',-1,prxchange('s/\n/" / ',-1,values))),'";');
end;
/* end code */

Takes a cell roughly like yours and converts it to
value gender
 1 = 'Male'
 2 = 'Female'
 9 = 'Unknown'
;
without actually giving separate data rows for it.

-Joe



On Mon, Jan 12, 2009 at 4:43 PM, Steve James <spj1@cdc.gov> wrote:

> I've got an MS Excel 2003 Workbook with cells that have multiple lines
> of data in it.  Lines are separated by a NewLine character.  I want to
> read it into SAS and put each line on a new observation
>
> I have
>
> Cell A1
> 1 = Male
> 2 = Female
> 9 = Unknown
>
> OBS    F1
> 1         1 = Male
> 2         2 = Female
> 3         9 = Unknown
>
> Any easy way to do this?
>
> Steve
>