Date:         Tue, 30 May 2006 17:10:03 -0400
Reply-To:     Chang Chung <chang_y_chung@HOTMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Chang Chung <chang_y_chung@HOTMAIL.COM>
Subject:      Re: From classification tree to spreadsheet
Comments: To: a.benedictus@GMAIL.COM

On Tue, 30 May 2006 13:28:45 -0700, Arjen <a.benedictus@GMAIL.COM> wrote:

>Hi guys,
>
>I imported a tab delimited outline of a classification tree (created in
>TreeAge) into SAS (via Excel). See below.
>
>Tree=Profile
>        Gender-Male
>                Smoking-Yes
>                        Age-Cat.1
>                                Habit1-Unk      Output=Smoking male/
>AgeCat=1/Habit1=unknown
>                                Habit1-Yes      Output=Output=Smoking male/
AgeCat=1/ Habit1=No
>                                Habit1-No       Output=Output=Smoking male/
AgeCat=1/ Habit1=No
>                        Age-Cat.2       Output=Smoking male/ AgeCat=2
>                        Age-Cat.3       Output=Smoking male/ AgeCat=3
>                Smoking-No      Output=Non-smoking male
>        Gender-female   Output=Woman
>
>
>I want to convert this outline to a spreadsheet. That means that for
>every output row (eg "Output= Smoking male Habit1=No"), I want to have
>all variables on the same row. See below.
>Gender-Male Smoking-Yes Habit1-Unk Output=Smoking male/
>AgeCat=1/Habit1=unknown
>Gender-Male Smoking-Yes Habit1-Yes Output=Smoking male/ AgeCat=1/
>Habit1=Yes
>Gender-Male Smoking-Yes Habit1-No Output=Smoking male/ AgeCat=1/
>Habit1=No
>Gender-Male Smoking-Yes Age-Cat.2 Output=Smoking male/ AgeCat=2
>Gender-Male Smoking-Yes Age-Cat.3 Output=Smoking male/ AgeCat=3
>Gender-female Output=Woman
>
>Any ideas on how to do this?

Hi, Arjen,

It seems that the Output= value has all the information. I would just delete
other rows in excel and, if neccessary, then re-create them from the output=
values. For example, knowning that the first value of output is "Smoking"
you can create "Smoking-Yes" with a simple spreadsheet formula. For example,
given the output value in the cell D3:

D3
---------
Smoking male/ AgeCat=1/ Habit1=Yes

Then, say in the B3 cell you can put a formula like:

B3
---------
="Smoking-" & if(find("Smoking", D3)=1, "Yes", "No")

Which will display in the B3 cell with "Smoking-Yes" or "Smoking-No"
depending on the first word in D3 is "Smoking" or not, respectively.
HTH.
Cheers,
Chang