f



Re: Deleting SAS Data from a SAS DATASET #9

On Fri, 15 Aug 2008 16:09:46 -0500, Mary <mlhoward@AVALON.NET> wrote:

>A view helps on deletes, but I wonder how it affects performance of
querying the data- wouldn't storing the data in 24 different locations
cause a significant slowdown in perfomance upon querying the data versus
having it all in one table that is indexed?  If this data is queryied a
lot but only deleted once a month, the time in querying (which probably is
in peak time) could be much more important than the time in deleting
(which could be run when the computer is not busy, such as nights or
weekends).

hi,
good point! but as shown below, you can make a view of multiple datasets
having the separate indexes, and where seems to be fine with this.
interesting. ran on sas 9.1.3 sp4 on windows.
cheers,
chang

/* test datasets */
data one two;
    set sashelp.class;
    ds = "one";
    output one;
    ds = "two";
    output two;
run;

/* separately index on age */
proc datasets lib=work;
  modify one;
  index delete age;
  index create age;
  modify two;
  index delete age;
  index create age;
quit;

/* make a view of two datasets appended */
proc sql feedback;
  drop view work.oneTwo;
  create view oneTwo as
  select * from one union select * from two
  order by age;
quit;

/* where is utilizing the indexes */
options msglevel=i;
data sixteenAndOlder;
  set oneTwo; /* we are using the view */
  where age >= 16;
run;
/* on log
INFO: Index Age selected for WHERE clause optimization.
INFO: Index Age selected for WHERE clause optimization.
*/
0
chang_y_chung (1975)
8/15/2008 10:03:52 PM
comp.soft-sys.sas 142828 articles. 3 followers. Post Follow

0 Replies
631 Views

Similar Articles

[PageSpeed] 54

Reply:

Similar Artilces:

Re: Deleting SAS Data from a SAS DATASET
One thing you might do is to add an index on the snap_dt to the dataset; = if that's there then you should be able to delete the records in place: proc sql; delete from prod.master_date; where snap_dt =3D "&end_dt"d; quit; run; In both the ways you are trying now you are creating new data sets = rather than deleting records from the current data set; it would seem to = me that a SQL delete statement would be faster than creating new = datasets even if there isn't an index on the date.=20 -Mary ----- Original Message -----=20 From: SUBSCRIBE SAS-L Chandra Gadd...

Re: Deleting SAS Data from a SAS DATASET #8
On 8/15/08, Mary <mlhoward@avalon.net> wrote: > A view helps on deletes, but I wonder how it affects performance of querying the data- wouldn't storing the data in 24 different locations cause a significant slowdown in perfomance upon querying the data versus having it all in one table that is indexed? If this data is queryied a lot but only deleted once a month, the time in querying (which probably is in peak time) could be much more important than the time in deleting (which could be run when the computer is not busy, such as nights or weekends). It is not the VIEW that has an...

Re: Deleting SAS Data from a SAS DATASET #19
On Mon, 18 Aug 2008 10:26:14 -0400, SUBSCRIBE SAS-L Chandra Gadde <ddraj2015@GMAIL.COM> wrote: >When I used this code, I am receiving the following warning. It completely >deleted my prod.master_date SAS Dataset. Do you know what is going on here? > >WARNING: This SAS global statement is not supported in PROC SQL. It has >been ignored. In PROC SQL, WHERE must be a clause within a statement, not a freestanding statement. Get rid of the semicolon immediately before the word "where". Incidentally, I think the warning is inaccurate in referring to a WHERE statemen...

Re: Deleting SAS Data from a SAS DATASET #3
The disadvantages of using SQL delete are (1) NOBS= is no longer accurate, and (2) POINT= may behave unexpectedly. If those are not issues, then the delete would be faster, with or without an index, than recreating the data set (especially if the data set has indexes that would need to be rebuilt). -- Jack Hamilton jfh@alumni.stanford.org On Aug 15, 2008, at 11:17 am, Mary wrote: > One thing you might do is to add an index on the snap_dt to the > dataset; if that's there then you should be able to delete the > records in place: > > proc sql; > delete from prod.ma...

Re: Deleting SAS Data from a SAS DATASET #4
Summary: PROC DATASETS; AGE statement. + VIEWs This won't help you delete data from your very big data set, but you may find this example interesting. You say you append data monthly to a big data set then when big gets too big you need to clean out the old. And that takes a very long time. However if you don't physically append but use a view to append/combine you may find it easier to get rid of the unwanted old data. Consider this code. it pushes MonthlyUpdate onto the stack of 24 data sets and the 24th data set is deleted. Then all the data sets get renamed to produce a new g...

Re: Deleting SAS Data from a SAS DATASET #10
On Fri, 15 Aug 2008 17:20:13 -0500, Mary <mlhoward@AVALON.NET> wrote: >And what about the time of the database administrator who now needs to keep track of 24 indexes to each index in the previous table? I just can't see our DB2 administrators would have ever thought to organize the data this way, though we had many very large tables, like the user had, that were purged by month; it does seem like it would be a lot of "people work" to manage 24 tables plus one index instead of just one table, even if querying didn't suffer(and I do think that it could). .... hi, Th...

Re: Deleting SAS Data from a SAS DATASET #11
And what about the time of the database administrator who now needs to = keep track of 24 indexes to each index in the previous table? I just = can't see our DB2 administrators would have ever thought to organize the = data this way, though we had many very large tables, like the user had, = that were purged by month; it does seem like it would be a lot of = "people work" to manage 24 tables plus one index instead of just one = table, even if querying didn't suffer(and I do think that it could).=20 -Mary ----- Original Message -----=20 From: Chang Chung=20 To: SAS-L@L...

Re: Deleting SAS Data from a SAS DATASET #5
I also read that this could be a problem on machines that have multiple = processors, such as running DB2; if the data is clustered and each = processor takes care of one cluster, then you could have queries all = hitting the same processor instead of distributing the load across all = processors, thus potentially slowing down queries in peak periods where = you have multiple queries at the same time, if queries tend to be based = on the month (such as bank statements). Thus I'd proceed with caution = on the idea of splitting one table into 24 tables with one view without = making sure th...

Re: Deleting SAS Data from a SAS DATASET #6
I have been watching this thread today and I just now recall an example that Paul Dorfman gave at a RUG several years ago and no, I don't recall exactly where or when other than it was NESUG or SESUG in the past 5 years. Anyway, Paul had a client who was storing something like the past 12 months of transactions as variables andat the start of each month needed to stick the just ended month on the end and drop the oldest month. Paul used Peek to read the last 11 months of each obs as a single chunk of data, append the current's month, and then used Poke to write the whole new obs as a ...

Re: Deleting SAS Data from a SAS DATASET #7
A view helps on deletes, but I wonder how it affects performance of = querying the data- wouldn't storing the data in 24 different locations = cause a significant slowdown in perfomance upon querying the data versus = having it all in one table that is indexed? If this data is queryied a = lot but only deleted once a month, the time in querying (which probably = is in peak time) could be much more important than the time in deleting = (which could be run when the computer is not busy, such as nights or = weekends). =20 -Mary ----- Original Message -----=20 From: ./ ADD NAME=3DData _n...

Re: Deleting SAS Data from a SAS DATASET #16
Summary: SAS Scalable Performance Data Server #iw-value=1 Chandra, You now have a number of reasonable suggestions to your problem that place the burden on the programmer, but I did not see mention of the SAS Scalable Performance Data Server. This is a SAS product designed to handle large amounts of data. Considering the size of your data set suggests that perhaps it is time to spend money on the problem. Even the SPDE engine included with the base product might help, particularly if the computer has several CPUs. Ian Whitlock =============== Date: Fri, 15 Aug 2008 13:14:22 -0400...

Re: Deleting SAS Data from a SAS DATASET #17
When I used this code, I am receiving the following warning. It completely deleted my prod.master_date SAS Dataset. Do you know what is going on here? WARNING: This SAS global statement is not supported in PROC SQL. It has been ignored. On Fri, 15 Aug 2008 13:17:25 -0500, Mary <mlhoward@AVALON.NET> wrote: >One thing you might do is to add an index on the snap_dt to the dataset; if that's there then you should be able to delete the records in place: > >proc sql; > delete from prod.master_date; > where snap_dt = "&end_dt"d; >quit; >run; > &...

Re: Deleting SAS Data from a SAS DATASET #18
Chandra, This is an excellent suggestion from Ian! Perhaps just recreate the = data set (you would in that case want to recreate, and thus NOT use the = SQL delete statement) once every 2-3 months and delete 2-3 months worth = of data at that time; it would seem that that wouldn't take too much = longer than deleting 1 month at a time, and thus the total time would be = reduced by as much as 50%. And this would meet my favorite efficiency marker, since I've met a lot = of people who don't want to work weekends and have yet to meet a = computer who cares, "less people time!...

Re: Deleting SAS Data from a SAS DATASET #2
Chandra - this isn't my expertise area - I'm sure others may have better ideas but I'll make three comments. If your datasets have a large number of variables, in your datastep method a WHERE clause will be more efficient - the IF statement causes each record to be fully read before it can be selected for deletion. A WHERE clause would only read the single variable in the case of records that meet the criteria for deletion. Data prod.Master_date; set prod.master_date; where snap_dt ne "&end_dt"d; run; You probably would be better off changing your data model t...

Web resources about - Re: Deleting SAS Data from a SAS DATASET #9 - comp.soft-sys.sas

Deleting Online Predators Act of 2006 - Wikipedia, the free encyclopedia
( DOPA ) is a bill ( H.R. 5319 ) brought before the United States House of Representatives on May 9, 2006 by Republican Pennsylvania Representative ...

Facebook Deleting Inflammatory Content In India
Facebook has been complying with a request by the government of India and removing “inflammatory and hateful content” that has caused tens of ...

Deleting your Facebook page is foolish
... brag, trailing closely behind that fantastically annoying “I don’t even have a TV , never mind watch the drivel!” Let’s lay it out: deleting ...

AdWords Campaign Experiments: Applying or deleting your experimental changes - YouTube
To improve your AdWords experience, we're testing a new tool that helps you optimize your account. AdWords Campaign Experiments, or ACE, does ...

Deleting abusive online content a 'near impossible' task
POSTING offensive, harassing, intimidating or defamatory content online takes mere seconds, but getting it removed can take what feels like a ...

Paleo advocate Pete Evans accused of deleting critical posts on Facebook
Is Paleo Pete Evans is open to dietary debate? Some say he's not.

Microsoft on the threshold of deleting 'appalling' Windows 8 software
MICROSOFT plans to cut its losses on Windows 8, the latest version of the software that runs the majority of the world's computers, jettisoning ...

How to get iOS 8 without deleting everything on your phone
If you've tried downloading iOS 8, or know anyone who has already done so, you're probably familiar with the biggest complaint: The download ...

Dalton McGuinty staffers broke law by deleting gas plant emails
Senior staff members in the offices of both the Ontario energy minister and former premier Dalton McGuinty intentionally deleted emails about ...

Jason Kenney under fire for praising ‘perfect, unaccented English’, deleting tweet
Canada's Minister for Multiculturalism, Jason Kenney, caused an upset Monday after praising an 11-year-old Iraqi refugee who "already speaks ...

Resources last updated: 2/4/2016 6:24:11 PM