Date:         Tue, 9 Mar 2004 11:37:55 +0100
Reply-To:     "Groeneveld, Jim" <jim.groeneveld@VITATRON.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Groeneveld, Jim" <jim.groeneveld@VITATRON.COM>
Subject:      Re: SORTEDBY after MERGE
Comments: To: Robert Burbidge <RBurbidge@PHD.CO.UK>
Content-Type: text/plain; charset="iso-8859-1"

Hi Robert,

If you add the following statements you will see, that the dataset was sorted anyway, but it didn't know until after an explicit SORT. SAS may use that info to decide not to resort if requested. The sorted status apparently is stored in the header part of a dataset. The status apparently is also set by the SORTEDBY option.

Regards - Jim.
--
.  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .

Jim Groeneveld, MSc.
Biostatistician
Science Team
Vitatron B.V.
Meander 1051
6825 MJ  Arnhem
Tel: +31/0 26 376 7365
Fax: +31/0 26 376 7305
Jim.Groeneveld@Vitatron.com
www.vitatron.com

My computer remains home, but I will attend SUGI 2004.

[common disclaimer]


-----Original Message-----
From: Robert Burbidge [mailto:RBurbidge@PHD.CO.UK]
Sent: Tuesday, March 09, 2004 11:13
To: SAS-L@LISTSERV.UGA.EDU
Subject: SORTEDBY after MERGE


Hi all,

Could someone explain why after merging two sorted tables, the result is not sorted?

<sas>
data test1;
  infile datalines;
  input ID;
  datalines;
  2
  1
  4
  5
  7
  3
;
data test2;
  infile datalines;
  input ID;
  datalines;
  3
  0
  -2
  0.1
  7
  .
;
proc sort data = test1; by ID;
proc sort data = test2; by ID;
data test3; merge test1 test2; by ID;
proc contents data = test3; run;
</sas>

There's an interesting (and divergent) thread pertaining to this in the archives:
http://listserv.uga.edu/cgi-bin/wa?A2=ind0110B&L=sas-l&D=0&P=15646

It seems that any DATA step will copy the table but not the SORTEDBY, even if the sort key is not changed (the docs suggest that the SORTEDBY is only overwritten if the sort key is manipulated in some way.)  E.g.:

<sas>
data test4; set test1;  /* test1 is sorted */
proc contents data = test4; run; /* test4 isn't! */
</sas>

What are the pitfalls of using the following?

<sas>
data test3 (sortedby = ID); merge test1 test2; by ID;
</sas>

[Release 8.02, Windows 5.1 (XP)]

Thanks+rgds,

Robert




***********************************************************************************
Privileged/Confidential Information may be contained in this message.
If you are not the addressee indicated in the message (or responsible
for the delivery of the message to such person), you may not copy
or deliver this message to anyone.
In such case, you should destroy this message and kindly notify the
sender by reply Email. Please advise immediately if you or your employer
does not consent to Internet Email for messages of this kind.
Opinions, conclusions and other information in this message that do not
relate to the official business of PHD Limited or its
Group/Associated Companies shall be understood as neither given nor
endorsed by them.
PHD Limited
Registered in England.
Registered Number: 2423952
Registered Office: The Telephone Exchange, 5 North Crescent, Chenies Street, London, WC1E 7PH
Telephone:       020 7446 0555
Fax:             020 7446 7100
E-Mail:         postmaster@phd.co.uk
***********************************************************************************