Date:         Thu, 26 Jan 2012 01:21:37 -0500
Reply-To:     Søren Lassen <s.lassen@POST.TELE.DK>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Søren Lassen <s.lassen@POST.TELE.DK>
Subject:      Re: Coding practices involving the Excel Libname
Comments: To: Nat Wooding <nathani@VERIZON.NET>
Content-Type: text/plain; charset=ISO-8859-1

Nat,
Apart from a possible case issue, there is also the fact that Excel (like
most databases) sorts nulls (missing values) last - SAS puts nulls
first.

When using e.g. Oracle, you can get around that issue by using NULLS FIRST,
e.g.:
proc sql;
  connect to oracle(<options>);
  create table x as select * from connection to oracle(
     select * from a order by
     x nulls first,y nulls first
  );

I do not know if there is a meaningful way of implementing the same
in Excel.

Regards,
Søren

On Tue, 24 Jan 2012 08:24:38 -0500, Nat Wooding <nathani@VERIZON.NET> wrote:

>Good time of the day to wherever you may be.
>
>
>
>A few minutes ago, I was playing with some sample code for a friend and in
>this code, I was reading the data from a workbook using the Libname engine.
>My initial code was similar to
>
>
>
>
>
>Libname getdata 'C:\workbook.xlsx';
>
>
>
>Data start;
>
>      Set getdata.'sheet1$'n;
>
>Run;
>
>
>
>Proc sort data = start ;
>
>By ..
>
>
>
>More SAS stuff that included a by statement in a data step.
>
>
>
>This did the job nicely but I decided to tighten the code a bit and get rid
>of the initial data step so it became
>
>
>
>Libname getdata 'C:\workbook.xlsx';
>
>
>
>Proc sort data = getdata.'sheet1$'n  out = start;
>
>By ..
>
>
>
>More SAS stuff..
>
>________________________________________________
>
>
>
>Now, SAS complains that the data are not sorted properly. A clue about this
>appears in the log:
>
>
>
>NOTE: Sorting was performed by the data source.
>
>NOTE: There were 576 observations read from the data set
>NO.'rate-variety_RV$'n.
>
>NOTE: The data set WORK.START has 576 observations and 24 variables
>
>
>
>
>
>So it appears that SAS told Excel to do the sorting and the Excel sort
order
>differs from that used by SAS. And, for once, here is an instance where
>seemingly sloppy coding is really needed.
>
>
>
>Nat Wooding