Date:         Thu, 3 Nov 2005 13:26:55 -0500
Reply-To:     Xu Libin <Libin.Xu@IRS.GOV>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Xu Libin <Libin.Xu@IRS.GOV>
Subject:      Re: Proc Export Question

I would like to extend my thanks for Terjeson, Mark (IM&R)
[Mterjeson@russell.com] Fehd, Ronald J [rjf2@CDC.GOV] David L Cassell for
their kind advice on how to export SAS data to an excel file. I experimented
with different approaches suggested and ended up exporting the data to an
csv file and manually changed the 12-Feb back to 2-12.
If proc format is used, the order is all right from the proc freq output.
But the excel file created by proc export gave you the value in the first
column while the csv file gave you the whole formatted value except 2-12 was
converted to date. I though I should be able to use mixed=yes to solve this
issue. It did not work.
If I created a new character variable with the following values
" 0- 1"
" 2-12"
"13-18"
"19-24"
"25-60"
"61+  "
The proc freq output would have the order like 0-1, 13-18, 19-24, 2-12,
25-60, and 61+. Obviously 2 is ordered after 1. The subsequent excel or csv
tables created by proc export had the wrong order.

Thank you again for your advice.
I would like to thank Ronald Fehd for sharing his program with me, which I
would keep for future reference.

Libin Xu

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of David L
Cassell
Sent: Tuesday, November 01, 2005 5:16 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: Proc Export Question

Libin.Xu@IRS.GOV wrote:
>I am running proc freq to get the distribution of age by group. Since age
>is
>a continuous variable, I tried to group age into several categories: < 1,
>2-12, 13-18, 19-24, 25-60, and > 60. I figure I can either use proc format
>for this purpose or create a new variable. The tables produced by proc freq
>with the two methods gave me different results for age. Table with the new
>variable did not order the age variable appropriately. Table using proc
>format had the right order for age, but when I exported to excel, I got 0,
>2, 13, 19, 25, and 61. If I exported to CSV file, I got age the way I want
>except 2-12, which became 12-Feb. I am puzzled. Anyone can solve the puzzle
>for me. Thanks.

I see a couple *different* issues here.

First off, the order you get when you use a proc like PROC FREQ is
*controllable*.
This is important to learn about.  The ORDER= option in the proc statement
gives
you a lot of flexibility here.

ORDER=DATA makes the proc order the values according to the order they are
in the data set.  So you can sort by some varible and ensure that you get
the
order the way you want it, regardless of the formatted or non-formatted
values.

ORDER=FORMATTED makes sure that the values are ordered by the formatted
values.  This might not do what you want, since you have a list of format
values
which won't sort the way you want.  If you arranged the format values like
this:
" 0- 1"
" 2-12"
"13-18"
"19-24"
"25-60"
"61+  "

then they might sort properly.  But as you have them constructed, the sort
order
would mess you up.

ORDER=INTERNAL means that the arrangement order is the *unformatted* order.
So, if you had sorted on that variable, you'd get this order.  If you have
age as a
numeric variable, that should work.

ORDER=FREQ is one you don't want.  It orders by descending frequency count.

So you may have messed up the order of the variable values by the way you
made
your format.


After that, the other problem you had was the way that Excel munged your CSV
file.
Right?  Your "2-12" looked fine in the CSV file, but Excel looked at it and
took a
guess that you had a Month-Date pair.  Just change the format a bit, and
that ought
to be okay.  I *think* that if you have a space on either side of the dash,
it will work
out.  But I haven't checked that.  You could stick an unprintable character
in there,
and that should *look* like a space, but keep Excel from parsing this as a
date.  Or
change the way Excel is displaying dates.

HTH,
David
--
David L. Cassell
mathematical statistician
Design Pathways
3115 NW Norwood Pl.
Corvallis OR 97330

_________________________________________________________________
Don't just search. Find. Check out the new MSN Search!
http://search.msn.click-url.com/go/onm00200636ave/direct/01/