|
Observations deleted with Proc FSEDIT are also marked as Paul described and
are not physically removed until the set is rewritten.
Nat Wooding
"Paul M. Dorfman"
<sashole@BELLSOUT To: SAS-L@LISTSERV.UGA.EDU
H.NET> cc:
Sent by: "SAS(r) Subject: Re: Deleted Observations in a dataset?
Discussion"
<SAS-L@LISTSERV.U
GA.EDU>
10/25/04 12:40 PM
Please respond to
sashole
Roy,
DELETE in SQL or REMOVE with MODIFY do not eliminate observations from a
data set physically, but only mark them as deleted. This makes for the
difference between physical observations and logical ones (returned by the
NOBS and NLOBS metadata variables). Now if you process a data set with some
observations marked as deleted, SAS disregards the deleted records, so
when,
for example, a new data set is being created in the process, only
observations not marked as deleted are written to the new output file. Try
sumbittin the following and examining the output as a proof:
data a ;
do var = 1 to 9 ; output ; end ;
run ;
proc sql ;
delete from a where var in (2, 4, 6) ;
quit ;
proc contents data = a ;
run ;
proc sql ;
create table b as select * from a ;
quit ;
proc contents data = b ;
run ;
Kind regards,
----------------
Paul M. Dorfman
Jacksonville, FL
----------------
> -----Original Message-----
> From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On
> Behalf Of Pardee, Roy
> Sent: Monday, October 25, 2004 12:06 PM
> To: SAS-L@LISTSERV.UGA.EDU
> Subject: Deleted Observations in a dataset?
>
> Esteemed collegues,
>
> I've got a proc contents header that looks like so:
>
> The SAS System 08:40 Monday, October
> 25, 2004 1
>
> The CONTENTS Procedure
>
> Data Set Name: Q.MBHIST Observations:
> 1584945
> Member Type: DATA Variables: 5
> Engine: V8 Indexes: 0
> Created: 13:54 Friday, October Observation Length: 40
> 22, 2004
> Last Modified: 14:13 Friday, October Deleted
> Observations: 2656116
> 22, 2004
> Protection: Compressed: NO
> Data Set Type: Sorted: YES
> Label:
>
> Please note the "Deleted Observations" heading.
>
> Now, when I say:
>
> data mylib.new ;
> set q.mbhist ;
> run ;
>
> Mylib.new is some 100+ MB smaller than q.mbhist. Can it be
> that the records I attempted to delete with code like so:
> proc sql ;
> delete from q.mbhist
> where PrimaryClinic not in (select SvcCtr from IGPClinics) ;
> quit ;
>
> Are somehow still taking up space in my dataset? My log on
> the delete operation just said "x records deleted from
> q.mbhist". The docs on sql's DELETE statement don't say
> anything about the records hanging about.
>
> Do I have to do a "compact & repair" or something on that
> dataset to make them really really go away? 8^)
>
> Thanks!
>
> -Roy
>
> Roy Pardee
> Research Analyst/Programmer
> Center For Health Studies (Cancer Research Network) Group
> Health Cooperative
> (206) 287-2078
>
> This message and any attached files might contain
> confidential information protected by federal and state law.
> The information is intended only for the use of the
> individual(s) or entities originally named as addressees. The
> improper disclosure of such information may be subject to
> civil or criminal penalties. If this message reached you in
> error, please contact the sender and destroy this message.
> Disclosing, copying, forwarding, or distributing the
> information by unauthorized individuals or entities is
> strictly prohibited by law.
>
| 
