Date: Wed, 14 Oct 2009 07:58:00 -0700
Reply-To: jfh@stanfordalumni.org
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Jack Hamilton <jfh@STANFORDALUMNI.ORG>
Subject: Re: SQL delete in Unix SAS
In-Reply-To: <92ea2e20-8c03-40bd-b256-2a0d5445c7b4@j4g2000yqa.googlegroups.com>
Content-Type: text/plain; charset="ISO-8859-1"
When you do a delete in SQL, SAS does a delete in place - the
observation is marked as deleted, but not actually removed from the data
set. So it continues to take up space, even though you can't get to it.
When you add new observations, they might go into the space previously
used by the deleted observations, depending on the value of the REUSE
data set option.
When you sort, the data set is rewritten - underneath, SAS creates a new
sorted copy, deletes the old copy, and renames the new copy to the old
name.
On Tue, 13 Oct 2009 13:49:59 -0700, "bayleo" <bayleo@GMAIL.COM> said:
> Hey guys,
>
> I have a few automated processes which use "sql delete" steps to
> remove whatever specified rows from my SAS datasets. The method is
> simple and easy, and I would like to continue using it, but for some
> reason the rows are not completely removed from the datasets unless I
> follow up with a proc sort...
>
> e.g. if I have a 20gig dataset and delete several million rows, I
> would expect the file size to visibly drop, but it does not. I have
> even tested this using "proc sql delete * " and the dataset remains
> the same size until I run a subsequent proc sort on whichever variable
> of my choosing and I get the message, "NOTE: Input data set is empty"
> in the log. The file then seems to revert to the correct size. Is
> there a step I'm missing here? Sorting it is all well and good but
> many of my datasets are indexed and can't easily be sorted.
>
> FYI I'm not exactly a SAS guru and it is running on a Solaris 8 box.
--
Jack Hamilton
Sacramento, California
jfh@alumni.stanford.org <== Use this, not jfh @ stanfordalumni.org
Tots units fem força!
| 
