Date: Mon, 16 Aug 2010 12:44:08 -0400
Reply-To: Suzanne McCoy <Suzanne.McCoy@CATALINAMARKETING.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Suzanne McCoy <Suzanne.McCoy@CATALINAMARKETING.COM>
Subject: Re: strange sorting problem - data from odbc connection
In-Reply-To: <OFBF96AC28.E6999351-ON85257781.00578869-85257781.00583B8F@aep.com>
Content-Type: text/plain; charset="us-ascii"
I'm glad that resolved your problem this time. Keep in mind that you are losing the power of the database and forcing SAS to pull all of the data across the connection. If your data is small this is a great solution. The data I deal with is millions to billions of rows so we definitely get the database to do everything where possible because it is more efficient and the DBA team can streamline stuff for us.
-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Jennifer R. Holdcraft
Sent: Monday, August 16, 2010 12:04 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: strange sorting problem - data from odbc connection
Thanks to everyone that responded - it definitely isn't an Oracle database
so I wasn't able to get some of the original debug=___ solutions to work,
and unfortunately I don't have 9.2 so Toby's sortseq solution wasn't
possible. However, I received an email today from Scott Mebust suggesting
this option:
options SORTPGM=SAS;
Worked perfectly!
Thanks again,
Jennifer
Jennifer R. Holdcraft
System Analyst, Market Risk Analytics
American Electric Power
614-583-6761, Audinet 220-6761
Cell: 614-307-4258
jrholdcraft@aep.com
toby dunn <tobydunn@hotmail.com>
08/13/2010 04:11 PM
To
<jrholdcraft@aep.com>, <sas-l@listserv.uga.edu>
cc
Subject
RE: strange sorting problem - data from odbc connection
Jennifer,
Well without knowing what the underlying "Live Data" is stored as and
baring any problems with the proc sort, Id say you have a EBCDIC to ASCII
conversion problem type thingy going on.
Try this:
Data Temp / View = Temp ;
Set raft.crrdefaultprobabilityimport ;
Run ;
Proc Sort
Data = Temp
Out = crrdefaultprobabilityimport ;
By Rating ;
Run ;
Proc Transpose
Data = crrdefaultprobabilityimport
Out = Probability ;
By Rating ;
Run ;
Now if your using V9.2 you can specify the sprt order you want with
SortSeq= option.
Toby Dunn
"I'm a hell bent 100% Texan til I die"
"Don't touch my Willie, I don't know you that well"
> Date: Fri, 13 Aug 2010 15:49:34 -0400
> From: jrholdcraft@AEP.COM
> Subject: strange sorting problem - data from odbc connection
> To: SAS-L@LISTSERV.UGA.EDU
>
> I just inherited some code from a guy in my department whose last day
was
> today and when asking him about one program earlier today, he realized
he
> been sourcing the data from a static SAS dataset that he had created a
> while back, rather than from the live source (it's some proprietary
> database that we have an odbc connection to) and just told me to change
> the libname references from that static location to the live one. Ok, no
> biggie. So all day I've been testing different things and after I made
> the particular change to hit the live source, the program is failing
> because it claims the data isn't sorted.
>
> His original code just had a direct sort from the source and made a work
> table, and then a transpose was made - and it had been working fine when
> the libname was CREDREF (where a SAS dataset of that name was located) -
> but when I changed the libname to RAFT (the odbc connection to live
> database) the transpose keeps failing and claiming the work dataset
> created in the proc sort isn't sorted.
>
> proc sort data=raft.crrdefaultprobabilityimport
> out=crrdefaultprobabilityimport;
> by rating;
> run;
>
> proc transpose data=work.crrdefaultprobabilityimport
out=work.probability;
> by rating;
> run;
>
> Now I'm getting this message in the log at the transpose - saying it's
not
> sorted (there was not an error in the log for the sorting):
>
> 86
> 87 proc transpose data=work.crrdefaultprobabilityimport
> out=work.probability;
> 88 by rating;
> 89 run;
>
> ERROR: Data set WORK.CRRDEFAULTPROBABILITYIMPORT is not sorted in
> ascending sequence. The
> current by-group has Rating = A- and the next by-group has Rating =
> A+.
> NOTE: The SAS System stopped processing this step because of errors.
> NOTE: There were 3 observations read from the data set
> WORK.CRRDEFAULTPROBABILITYIMPORT.
> WARNING: The data set WORK.PROBABILITY may be incomplete. When this step
> was stopped there
> were 0 observations and 0 variables.
> WARNING: Data set WORK.PROBABILITY was not replaced because this step
was
> stopped.
> NOTE: PROCEDURE TRANSPOSE used (Total process time):
> real time 0.00 seconds
> cpu time 0.00 seconds
>
>
> I wondered if it was just related to proc sort somehow, and tried to use
> proc sql with an order before the transpose, but that didn't work
either.
> I finally got a work around by just doing a datastep set from the
source,
> then sorting that work dataset, and then transposing...
>
> data work.crrdefaultprobabilityimport;
> set raft.crrdefaultprobabilityimport;
> run;
>
> proc sort data=work.crrdefaultprobabilityimport; by rating; run;
>
> proc transpose data=work.crrdefaultprobabilityimport
> out=work.probability;
> by rating;
> run;
>
> but can anyone tell me what is happening and if there's some option or
> something else I can do, rather than the clumsy work around I have now?
> I've never seen this before...
>
>
> Thanks,
>
>
> Jennifer R. Holdcraft
> System Analyst, Market Risk Analytics
> American Electric Power
> 614-583-6761, Audinet 220-6761
> Cell: 614-307-4258
> jrholdcraft@aep.com
