LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (October 2009, week 1)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
Date:         Tue, 6 Oct 2009 08:47:13 -0400
Reply-To:     Lou <lpogoda@HOTMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Lou <lpogoda@HOTMAIL.COM>
Organization: A noiseless patient Spider
Subject:      Re: PROC SQL Error
Comments: To: sas-l@uga.edu

""Kirby, Ted"" <ted.kirby@LEWIN.COM> wrote in message news:100FCDB28E638D4B903CB7D2056E43C715FFA4@USFCH-MAIL1.lewin.com... >I have a very large dataset, FinalEpisodes2 (70.5 GB with binary = > compression, 182 million observations and 199 variables). I deleted = > 55.5 million observations from the dataset, but the size on disk did not = > change.

If the size of the dataset did not change, then you did not physically remove the observations. Using a PROC to delete observations, for instance using the delete command in PROC FSEDIT or DELETE FROM in PROC SQL, simply marks observations for deletion. The data on those observations is no longer available for processing, but physically, they're still in the dataset.

To physically remove observations, the dataset must be rewritten. Using a DATA step is the usual way to do this.

I've never seen or heard of the error messages you post below, and can't comment beyond saying that I wouldn't trust the output of any process that generated any error message, let alone multiple error messages. (nothing new below, included for reference only)

Then I attempted to delete 9 of the variables with this code: > =20 > proc sql; > alter table ep.FinalEpisodes2=20 > drop LTCH, SNF, IRF, HHA, STACH, LTCHAll, oldlb, oldnep, oldet; > quit; > =20 > When I tried to delete these variables, I received a bunch of SAS = > errors. See the log below: > =20 > > NOTE: Remote submit to QLEWSAS2 commencing. > > 17 proc sql; > > 18 alter table ep.FinalEpisodes2 > > 19 drop LTCH, SNF, IRF, HHA, STACH, LTCHAll,oldlb,oldnep,oldet; > > ERROR: Permanent copy of file EP.SQ_1.DATA was deleted. > > ERROR: Permanent copy of file EP.SQ_1.DATA was deleted. > > =20 > > =20 > > =20 > > Invalid free() attempt to deleted pool > > Traceback > > SASTBTraceBackCtx has been called with a > > string ("vacrash") instead of a CONTEXT pointer. > > Address Frame (DBGHELP API Version 4.0 rev 5) > > 5C8D1FCD 01A0EE60 tkmk:tkBoot+0x20FA9 > > 5C8D1DCF 01A0EE6C tkmk:tkBoot+0x20DAB > > 5C8C8236 01A0EE8C tkmk:tkBoot+0x17212 > > 00FF78E3 01A0EEAC sashost:Main+0xD0C53 > > 00F2D974 01A0EECC sashost:Main+0x6CE4 > > 00F21A33 01A0EF24 sashost:rtmdoit+0x1DF > > 00F3D0B2 01A0EF38 sashost:Main+0x16422 > > 00F3A5B6 01A0EF54 sashost:Main+0x13926 > > 678F25ED 01A0EF98 sasyh:mcn_main+0x15ED > > 671A1A25 01A0F2C0 sasyoio:mcn_main+0xA25 > > 6004EE2D 01A0FEA0 sassqlu:mcn_main+0x1DE2D > > 02AB333F 01A0FF58 sassqx:mcn_main+0x2233F > > 600D118A 01A0FF8C sassql:mcn_main+0x18A > > 00F32B02 01A0FFA4 sashost:Main+0xBE72 > > 00F36C10 01A0FFB8 sashost:Main+0xFF80 > > 77E64829 01A0FFEC kernel32:GetModuleHandleA+0xDF > > =20 > > =20 > > ERROR: User asked for termination > > NOTE: The SAS System stopped processing this step because of errors. > > NOTE: PROCEDURE SQL used (Total process time): > > real time 1:28:31.88 > > cpu time 34:41.43 > > =20 > > NOTE: SAS Institute Inc., SAS Campus Drive, Cary, NC USA 27513-2414 > > NOTE: The SAS System used: > > real time 5:16:43.25 > > cpu time 1:12:29.39 > > =20 > > NOTE: Remote submit to QLEWSAS2 complete. > > =20 > Now there is a dataset in the folder called "sq_1.sas7bdat" This = > dataset is mentioned in the error code above. It appears that this = > dataset may be the dataset I was trying to create. It has the same = > number of observations as the 70.6 GB dataset (i.e., 182 million minus = > 55.5 million) and the number of variables in would be consistent with = > deleting the 9 variables that I had intended to delete (i.e., it has 190 = > variables). Furthermore, it is "only" 48 GB in size. > =20 > Does anybody know what happened? Is it safe to use the "sq_1" dataset = > since it appears to be what I wanted? > =20 > I am running SAS 9.1.3 on Windows XP professional as is the SAS Server = > to which I remotely submitted this code. > =20 > Thanks for any help you can give. > =20 > > ************* IMPORTANT - PLEASE READ ******************** > > This e-mail, including attachments, may include confidential and/or propr= > ietary information, and may be used only by the person or entity to which= > it is addressed. If the reader of this e-mail is not the intended recipi= > ent or his or her authorized agent, the reader is hereby notified that an= > y dissemination, distribution or copying of this e-mail is prohibited. If= > you have received this e-mail in error, please notify the sender by repl= > ying to this message and delete this e-mail immediately. > =0D


Back to: Top of message | Previous page | Main SAS-L page