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 though - my
first thought would to be to store your data as separate yearly or
monthly files and then access them with a view of the past two years or
24 months. With each month or year you would delete the oldest file and
change the view one time period forward.
*set up the data;
data time1 time2 time3 time4;
do i = 1 to 100;
do t = 1 to 4;
if t=1 then output time1;
if t=2 then output time2;
if t=3 then output time3;
if t=4 then output time4;
end;
end;
run;
*three successive example views;
data filet1 / view=filet1;
set time1 time2;
run;
data filet2 / view=filet2;
set time2 time3;
run;
data filet3 / view=filet3;
set time3 time4;
run;
One more thought - since the data are appended - if the file is not
reordered and you have the record counts from each append - then you can
use firstobs to only read starting with the recent time period. I would
replace the append with a datastep that skips the appended data from 24
months prior and appends the new data in a single pass:
*set up the data;
data history time2 time3 time4;
do i = 1 to 100;
do t = 1 to 4;
if t=1 then output history;
if t=2 then output time2;
if t=3 then output time3;
if t=4 then output time4;
end;
end;
run;
proc append base=history data=time2;
run;
*two example single pass delete/appends;
data history;
do obsnum=101 to last;
set history time3 point=obsnum nobs=last;
output;
end;
stop;
run;
data history;
do obsnum=101 to last;
set history time4 point=obsnum nobs=last;
output;
end;
stop;
run;
You'll need to store prior record counts and then access them for the
obsnum starting position.
hth
Paul Choate
DDS Data Extraction
(916) 654-2160
-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
SUBSCRIBE SAS-L Chandra Gadde
Sent: Friday, August 15, 2008 10:14 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Deleting SAS Data from a SAS DATASET
Hi All
I have several SAS datasets that are very very big. (50GB of size).
Every
month, the data is being appended to these datasets. I need to deleted
the
data which is greater than 24 months. What is the best method to do
this?
Please help me.
I tried PROC SQS and DATA STEP. But these two are taking very long time.
Data prod.Master_data;
set prod.master_date;
if snap_dt = "&end_dt"d then delete;
run;
proc sql;
crete table prod.master_date from prod.master_date
where snap_dt ne "&end_dt"d;
quit;
pchoate
comp.soft-sys.sas
142828 articles. 
3 followers.
0 Replies
968 Views
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 Gadde=20 To: SAS-L@LISTSERV.UGA.EDU=20 Sent: Friday, August 15, 2008 12:14 PM Subject: Deleting SAS Data from a SAS DATASET Hi All I have several SAS datasets that are very very big. (50GB of size). = Every month, the data is being appended to these datasets. I need to deleted = the data which is greater than 24 months. What is the best method to do = this? Please help me. I tried PROC SQS and DATA STEP. But these two are taking very long = time. Data prod.Master_data; set prod.master_date; if snap_dt =3D "&end_dt"d then delete; run; proc sql; crete table prod.master_date from prod.master_date where snap_dt ne "&end_dt"d; quit; Hi You've got good ideas how to delete the old observations. My modify proposal was wrong. Sorry about that! I still think that changing the data model would be a good idea. Regards Patrick ...
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 statement as a global statement. > > > >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; >> >>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. >> >>-Mary >> ----- Original Message ----- >> From: SUBSCRIBE SAS-L Chandra Gadde >> To: SAS-L@LISTSERV.UGA.EDU >> Sent: Friday, August ...
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.master_date; > where snap_dt = "&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. > > -Mary > ----- Original Message ----- > From: SUBSCRIBE SAS-L Chandra Gadde > To: SAS-L@LISTSERV.UGA.EDU > Sent: Friday, August 15, 2008 12:14 PM > Subject: Deleting SAS Data from a SAS DATASET > > > Hi All > > I have several SAS datasets that are very very big. (50GB of size). > Every > month, the data is being appended to these datasets. I need to > deleted the > data which is greater than 24 months. What is the best method to do > this? > Please help me. > ...
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 optimiz...
Re: Deleting SAS Data from a SAS DATASET #12
--On 8/15/08 6:03 PM -0400 Chang Chung wrote: > 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. > /* 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; One thought on this--at least in my general approach to using views--is you probably want to avoid the ORDER BY statement--especially if it's the variable you're indexing on and you want to use a subset. For any amount of data above a trivial one, there's no sense in forcing the view to re-order th e data if it doesn't have to. Dave ...
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 unit. The time reduction was dramatic. It probably would not work here unless you knew a whole lot about the structure of the data set and it has a very fixed structure but I was so impressed with the solution that I like to mention it whenever it seems even remotely useful. Nat Wooding Environmental Specialist III Dominion, Environmental Biology 4111 Castlewood Rd Richmond, VA 23234 Phone:804-271-5313, Fax: 804-271-2977 CONFIDENTIALITY NOTICE: This electronic message contains information which may be legally confidential and/or privileged and does not in any case represent a firm ENERGY COMMODITY bid or offer relating thereto which binds the sender without an additional express written confirmation to that effect. The information is intended solely for the individual or entity named above and access by anyone else is unauthorized. If you are not the intended recipient, any disclos...
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 influence on deleting the old data. I would think that having the 24 indexed data sets might be about as good as having the giant data set. I would think the indexes could be used while accessing the data through views. Where's that guy that says he knows everything about using indexed data sets? I would agree that much depends on how the data is used. And I don't know the answers to those questions. > > -Mary > ----- Original Message ----- > From: ./ ADD NAME=Data _null_, > To: SAS-L@LISTSERV.UGA.EDU > Sent: Friday, August 15, 2008 3:51 PM > Subject: Re: Deleting SAS Data from a SAS DATASET > > > 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 b...
Re: Deleting SAS Data from a SAS DATASET #13
I am not expert with indexes, but I would expect that an index on each data set would be employed even when the data are accessed through a view. If that is correct, there would be clear advantage to indexing data for each month separately. If the data are in one large file, then when old data are deleted and new data added, the entire index will need to be recreated on all of the data. That is a big operation. If, instead, data are maintained in separate files for each month and each month of data is indexed independently, then indexing needs to be performed only for the new data. The indexes for months which do not age out would not need to be touched. Advantage, VIEWS. Dale --------------------------------------- Dale McLerran Fred Hutchinson Cancer Research Center mailto: dmclerra@NO_SPAMfhcrc.org Ph: (206) 667-2926 Fax: (206) 667-5977 --------------------------------------- --- On Fri, 8/15/08, ./ ADD NAME=Data _null_, <iebupdte@GMAIL.COM> wrote: > From: ./ ADD NAME=Data _null_, <iebupdte@GMAIL.COM> > Subject: Re: Deleting SAS Data from a SAS DATASET > To: SAS-L@LISTSERV.UGA.EDU > Date: Friday, August 15, 2008, 2:59 PM > 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...
Re: Deleting SAS Data from a SAS DATASET #15
Summary: Suggestions for maintaining large data set #iw-value=1 Chandra, You are essentially making a copy of a 50GB file every month. Of course, it takes long. Consequently you either have to copy the data more efficiently or modify how the data is stored and/or used. Have you looked at system options to make this as efficient as possible? Big buffers, many buffers. Different disk handlers for input and output would also speed up the process since it is mainly IO bound. (These topics have been discussed on SAS-L, in papers, and documentation. I have not found the techniques necessary, but then I have not worked with 50 GB data sets.) You are adding about 2 gig per month. That is 4% increase. Perhaps accepting the increase one month and then modifying the second month would make more sense, if the data is accessed infrequently. To do this you need to control all programs that read the data set. It might pay to combine the previous suggestion with the MODIFY command. I suspect that SAS can skip over deleted records pretty quickly. It might pay to store the data by month and each month create a new view data prod.master / view = prod.master ; set prod.mon_x ... prod.mon_y ; run ; The process could even be automated with something like %macro prod_list ( end_dt = 1jul2008 ) ; %local i mon ; %do i = -11 %to 0 ; %let mon = %sysfunc(intnx(month,"&end_dt"d,&i)) ; %let mon = %sysfunc(putn(...
Re: Deleting SAS Data from a SAS DATASET #14
On Fri, 15 Aug 2008 18:54:08 -0400, Dave Scocca <dave@SCOCCA.ORG> wrote: >--On 8/15/08 6:03 PM -0400 Chang Chung wrote: > >> 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. > >> /* 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; > >One thought on this--at least in my general approach to using views--is you >probably want to avoid the ORDER BY statement--especially if it's the variable >you're indexing on and you want to use a subset. > >For any amount of data above a trivial one, there's no sense in forcing the >view to re-order th >e data if it doesn't have to. Eliminating ORDER BY is not going to do any good unless the ALL option is invoked for the UNION operator. Otherwise the view is going to purge duplicate rows, and will sort the rows to support that process. Ian mentioned the ALL option but did not emphasize its importance. > >Dave ...
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 group of 24. You can see from the notes how the operation works. The data sets don't have to use a numbered range M01-M24 I did that for my convenience. proc datasets library=work; age MonthlyUpdate m01-m24; run; quit; NOTE: Deleting WORK.M24 (memtype=DATA). NOTE: Aging the name WORK.M23 to WORK.M24 (memtype=DATA). NOTE: Aging the name WORK.M22 to WORK.M23 (memtype=DATA). NOTE: Aging the name WORK.M21 to WORK.M22 (memtype=DATA). NOTE: Aging the name WORK.M20 to WORK.M21 (memtype=DATA). NOTE: Aging the name WORK.M19 to WORK.M20 (memtype=DATA). NOTE: Aging the name WORK.M18 to WORK.M19 (memtype=DATA). NOTE: Aging the name WORK.M17 to WORK.M18 (memtype=DATA). NOTE: Aging the name WORK.M16 to WORK.M17 (memtype=DATA). NOTE: Aging the name WORK.M15 to WORK.M16 (memtype=DATA). NOTE: Aging the name WORK.M14 to WORK.M15 (memtype=DATA). NOTE: Aging the name WORK.M13 to WORK.M14 (me...
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 the other consequences of doing so are fully understood. -Mary ----- Original Message -----=20 From: Mary=20 To: SAS-L@LISTSERV.UGA.EDU=20 Sent: Friday, August 15, 2008 4:09 PM Subject: Re: Re: Deleting SAS Data from a SAS DATASET 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 ...
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 _null_,=20 To: SAS-L@LISTSERV.UGA.EDU=20 Sent: Friday, August 15, 2008 3:51 PM Subject: Re: Deleting SAS Data from a SAS DATASET 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 group of 24. You can see from the notes how the operation works. The data sets don't have to use a numbered range M01-M24 I did that...
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!" -Mary ----- Original Message -----=20 From: Ian Whitlock=20 To: SAS-L@LISTSERV.UGA.EDU=20 Sent: Friday, August 15, 2008 6:07 PM Subject: Re: Deleting SAS Data from a SAS DATASET Summary: Suggestions for maintaining large data set #iw-value=3D1 Chandra, You are adding about 2 gig per month. That is 4% increase. Perhaps accepting the increase one month and then modifying the second month would make more sense, if the data is accessed infrequently. To do this you need to control all programs that read the data set. ...
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; > >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. > >-Mary > ----- Original Message ----- > From: SUBSCRIBE SAS-L Chandra Gadde > To: SAS-L@LISTSERV.UGA.EDU > Sent: Friday, August 15, 2008 12:14 PM > Subject: Deleting SAS Data from a SAS DATASET > > > Hi All > > I have several SAS datasets that are very very big. (50GB of size). Every > month, the data is being appended to these datasets. I need to deleted the > data which is greater than 24 months. What is the best method to do this? > Please help me. > > I tried PROC SQS and DATA STEP. But these two are taking very long time. > > Data prod.Master_data; > ...
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 Reply-To: SUBSCRIBE SAS-L Chandra Gadde <ddraj2015@GMAIL.COM> Sender: "SAS(r) Discussion" From: SUBSCRIBE SAS-L Chandra Gadde <ddraj2015@GMAIL.COM> Subject: Deleting SAS Data from a SAS DATASET Hi All I have several SAS datasets that are very very big. (50GB of size). Every month, the data is being appended to these datasets. I need to deleted the data which is greater than 24 months. What is the best method to do this? Please help me. I tried PROC SQS and DATA STEP. But these two are taking very long time. Data prod.Master_data; set prod.master_date; if snap_dt = "&end_dt"d then delete; run; proc sql; crete table prod.master_date from prod.master_date where snap_dt ne "&end_dt"d; quit; ...
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, There are only 24, not like thousands or millions! :-) and with any DB system, if you have a huge table, then it is more likely that it will map to multiple physical files behind the scene anyway. In terms of querying speed, i rather think it can be faster. One way is to spawn many sas sessions to do multiple separate queries at the same time, then put together... sort of like do-it-yourself parallel processing. implementation is left as homework. happy friday! cheers, chang ...
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@LISTSERV.UGA.EDU=20 Sent: Friday, August 15, 2008 5:03 PM Subject: Re: Deleting SAS Data from a SAS DATASET 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 =3D "one"; output one; ds =3D "two"; output two; run; /* separately index on age */ proc datasets lib=3Dwork; 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 utili...
Re: SAS Macro to delete a SAS dataset #2 313487
I too like(d) PROC DELETE. When I used it I was forced to remove it and use PROC DATASETS because PROC DELETE is not documented. Also, but I have not tested it. How does it work with indexed data sets, audit trails, generation data groups, views, etc.? I don't worry much about deleting, "all" of my programs run in batch and are obsolete as soon as they are written. On 12/3/09, Fehd, Ronald J. (CDC/CCHIS/NCPHI) <rjf2@cdc.gov> wrote: > > From: SAS Techies > > Subject: SAS Macro to delete a SAS dataset > > > > Here's a simple SAS...
Deleting SAS Data from a SAS DATASET
Hi All I have several SAS datasets that are very very big. (50GB of size). Every month, the data is being appended to these datasets. I need to deleted the data which is greater than 24 months. What is the best method to do this? Please help me. I tried PROC SQS and DATA STEP. But these two are taking very long time. Data prod.Master_data; set prod.master_date; if snap_dt = "&end_dt"d then delete; run; proc sql; crete table prod.master_date from prod.master_date where snap_dt ne "&end_dt"d; quit; Hi You could use a 'modify' statement (data step). This statement does the changes in place and needs therefore only have of the space, BUT: there is a risk of corrupting your data; look it up in the manual. I also think that some re-designing could solve some problems and improve performance. I assume you're loading kind of a data mart with a history of 24 months. What you could do (just an idea): - Create a SAS file per month (eg: prod.master_date&yyyymm) - Create a SAS view for the most recent 24 SAS files (prod.master). - Create an index over the date field. Advantages: - Loading of new data will be much faster (create new file with current month, re-create view, delete SAS file with data older 24 months). - As the SAS view has the same name like previously the SAS data file your users won't even get that something changed (assuming they only read the data). - Queries using the index will perform much faster. - SAS power...
Re: What r the data types in SAS? in Base SAS , and SAS SQL
> From: Amar Mundankar > Sent: Tuesday, July 21, 2009 8:10 AM > To: sas-l@uga.edu > Subject: What r the data types in SAS? in Base SAS , and SAS SQL > > Hi all, > What are the different data types in Base SAS and SAS SQL?? character, lengths from 1 to 32,000+ numeric: lengths from 2 to 8 dates are a subtype of numeric and are identified by their date, datetime, or time formats the new proc TSPL (Table Server Processing Language) supports ANSI data types: bigint, tinyint, etc. http://support.sas.com/documentation/cdl/en/tsag/30878/HTML/default/a003 065339.htm http://s...
Re: SAS Macro to delete a SAS dataset 196997
> From: SAS Techies > Subject: SAS Macro to delete a SAS dataset > > Here's a simple SAS Macro to delete a SAS dataset...It takes the name of > the SAS dataset as the parameter... > > %macro deletedsn(dsname); > > %if %index(&dsname,'.') eq 0 %then %do; %let lib=work; %let > dsn=&dsname; %end; > %else %if %index(&dsname,'.') eq 0 %then %do; %let lib=%scan(&dsname, > 1,'.'); %let dsn=%scan(&dsname,1,'.'); %end; > > proc datasets lib=&lib nolist; > delete &dsn; > qui...
Re: search SAS data set from SAS code #2
Rose, The answer to your question depends on your operating system. In Windows, there's the Search tool. In Unix/Linux, you can use grep Bob Abelson HGSI 240 314 4400 x1374 bob_abelson@hgsi.com Rose <myr_rose@YAHOO.COM> Sent by: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU> 04/19/2005 11:13 AM Please respond to myr_rose To: SAS-L@LISTSERV.UGA.EDU cc: Subject: search SAS data set from SAS code Hi All, Suppose I have a sas permanent data set which was created early, I know the library path but I couldn't remember in which s...
Re: Is it possible to read a sas dataset NOT using sas? #2
I think SPSS and DBMSCopy can read SAS datasets. Also for sure WPS. Gerhard On Tue, 18 Mar 2008 06:43:44 -0700, RolandRB <rolandberry@HOTMAIL.COM> wrote: >Is it possible to read a sas dataset NOT using sas and have >observations and variables etc. rather than having meaningless chunks >of text? ...
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 Gadde=20 To: SAS-L@LISTSERV.UGA.EDU=20 Sent: Friday, August 15, 2008 12:14 PM Subject: Deleting SAS Data from a SAS DATASET Hi All I have several SAS datasets that are very very big. (50GB of size). = Every month, the data is being appended to these datasets. I need to deleted = the data which is greater than 24 months. What is the best method to do = this? Please help me. I tried PROC SQS and DATA STEP. But these two are taking very long = time. Data prod.Master_data; set prod.master_date; if snap_dt =3D "&end_dt"d then delete; run; proc sql; crete table prod.master_date from prod.master_date where snap_dt ne "&end_dt"d; quit; Hi You've got good ideas how to delete the old observations. My modify proposal was wrong. Sorry about that! I still think that changing the data model would be a good idea. Regards Patrick ...
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 statement as a global statement. > > > >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; >> >>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. >> >>-Mary >> ----- Original Message ----- >> From: SUBSCRIBE SAS-L Chandra Gadde >> To: SAS-L@LISTSERV.UGA.EDU >> Sent: Friday, August ...
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.master_date; > where snap_dt = "&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. > > -Mary > ----- Original Message ----- > From: SUBSCRIBE SAS-L Chandra Gadde > To: SAS-L@LISTSERV.UGA.EDU > Sent: Friday, August 15, 2008 12:14 PM > Subject: Deleting SAS Data from a SAS DATASET > > > Hi All > > I have several SAS datasets that are very very big. (50GB of size). > Every > month, the data is being appended to these datasets. I need to > deleted the > data which is greater than 24 months. What is the best method to do > this? > Please help me. > ...
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 optimiz...
Re: Deleting SAS Data from a SAS DATASET #12
--On 8/15/08 6:03 PM -0400 Chang Chung wrote: > 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. > /* 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; One thought on this--at least in my general approach to using views--is you probably want to avoid the ORDER BY statement--especially if it's the variable you're indexing on and you want to use a subset. For any amount of data above a trivial one, there's no sense in forcing the view to re-order th e data if it doesn't have to. Dave ...
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 unit. The time reduction was dramatic. It probably would not work here unless you knew a whole lot about the structure of the data set and it has a very fixed structure but I was so impressed with the solution that I like to mention it whenever it seems even remotely useful. Nat Wooding Environmental Specialist III Dominion, Environmental Biology 4111 Castlewood Rd Richmond, VA 23234 Phone:804-271-5313, Fax: 804-271-2977 CONFIDENTIALITY NOTICE: This electronic message contains information which may be legally confidential and/or privileged and does not in any case represent a firm ENERGY COMMODITY bid or offer relating thereto which binds the sender without an additional express written confirmation to that effect. The information is intended solely for the individual or entity named above and access by anyone else is unauthorized. If you are not the intended recipient, any disclos...
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 influence on deleting the old data. I would think that having the 24 indexed data sets might be about as good as having the giant data set. I would think the indexes could be used while accessing the data through views. Where's that guy that says he knows everything about using indexed data sets? I would agree that much depends on how the data is used. And I don't know the answers to those questions. > > -Mary > ----- Original Message ----- > From: ./ ADD NAME=Data _null_, > To: SAS-L@LISTSERV.UGA.EDU > Sent: Friday, August 15, 2008 3:51 PM > Subject: Re: Deleting SAS Data from a SAS DATASET > > > 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 b...
Re: Deleting SAS Data from a SAS DATASET #13
I am not expert with indexes, but I would expect that an index on each data set would be employed even when the data are accessed through a view. If that is correct, there would be clear advantage to indexing data for each month separately. If the data are in one large file, then when old data are deleted and new data added, the entire index will need to be recreated on all of the data. That is a big operation. If, instead, data are maintained in separate files for each month and each month of data is indexed independently, then indexing needs to be performed only for the new data. The indexes for months which do not age out would not need to be touched. Advantage, VIEWS. Dale --------------------------------------- Dale McLerran Fred Hutchinson Cancer Research Center mailto: dmclerra@NO_SPAMfhcrc.org Ph: (206) 667-2926 Fax: (206) 667-5977 --------------------------------------- --- On Fri, 8/15/08, ./ ADD NAME=Data _null_, <iebupdte@GMAIL.COM> wrote: > From: ./ ADD NAME=Data _null_, <iebupdte@GMAIL.COM> > Subject: Re: Deleting SAS Data from a SAS DATASET > To: SAS-L@LISTSERV.UGA.EDU > Date: Friday, August 15, 2008, 2:59 PM > 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...
Re: Deleting SAS Data from a SAS DATASET #15
Summary: Suggestions for maintaining large data set #iw-value=1 Chandra, You are essentially making a copy of a 50GB file every month. Of course, it takes long. Consequently you either have to copy the data more efficiently or modify how the data is stored and/or used. Have you looked at system options to make this as efficient as possible? Big buffers, many buffers. Different disk handlers for input and output would also speed up the process since it is mainly IO bound. (These topics have been discussed on SAS-L, in papers, and documentation. I have not found the techniques necessary, but then I have not worked with 50 GB data sets.) You are adding about 2 gig per month. That is 4% increase. Perhaps accepting the increase one month and then modifying the second month would make more sense, if the data is accessed infrequently. To do this you need to control all programs that read the data set. It might pay to combine the previous suggestion with the MODIFY command. I suspect that SAS can skip over deleted records pretty quickly. It might pay to store the data by month and each month create a new view data prod.master / view = prod.master ; set prod.mon_x ... prod.mon_y ; run ; The process could even be automated with something like %macro prod_list ( end_dt = 1jul2008 ) ; %local i mon ; %do i = -11 %to 0 ; %let mon = %sysfunc(intnx(month,"&end_dt"d,&i)) ; %let mon = %sysfunc(putn(...
Re: Deleting SAS Data from a SAS DATASET #14
On Fri, 15 Aug 2008 18:54:08 -0400, Dave Scocca <dave@SCOCCA.ORG> wrote: >--On 8/15/08 6:03 PM -0400 Chang Chung wrote: > >> 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. > >> /* 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; > >One thought on this--at least in my general approach to using views--is you >probably want to avoid the ORDER BY statement--especially if it's the variable >you're indexing on and you want to use a subset. > >For any amount of data above a trivial one, there's no sense in forcing the >view to re-order th >e data if it doesn't have to. Eliminating ORDER BY is not going to do any good unless the ALL option is invoked for the UNION operator. Otherwise the view is going to purge duplicate rows, and will sort the rows to support that process. Ian mentioned the ALL option but did not emphasize its importance. > >Dave ...
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 group of 24. You can see from the notes how the operation works. The data sets don't have to use a numbered range M01-M24 I did that for my convenience. proc datasets library=work; age MonthlyUpdate m01-m24; run; quit; NOTE: Deleting WORK.M24 (memtype=DATA). NOTE: Aging the name WORK.M23 to WORK.M24 (memtype=DATA). NOTE: Aging the name WORK.M22 to WORK.M23 (memtype=DATA). NOTE: Aging the name WORK.M21 to WORK.M22 (memtype=DATA). NOTE: Aging the name WORK.M20 to WORK.M21 (memtype=DATA). NOTE: Aging the name WORK.M19 to WORK.M20 (memtype=DATA). NOTE: Aging the name WORK.M18 to WORK.M19 (memtype=DATA). NOTE: Aging the name WORK.M17 to WORK.M18 (memtype=DATA). NOTE: Aging the name WORK.M16 to WORK.M17 (memtype=DATA). NOTE: Aging the name WORK.M15 to WORK.M16 (memtype=DATA). NOTE: Aging the name WORK.M14 to WORK.M15 (memtype=DATA). NOTE: Aging the name WORK.M13 to WORK.M14 (me...
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 the other consequences of doing so are fully understood. -Mary ----- Original Message -----=20 From: Mary=20 To: SAS-L@LISTSERV.UGA.EDU=20 Sent: Friday, August 15, 2008 4:09 PM Subject: Re: Re: Deleting SAS Data from a SAS DATASET 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 ...
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 _null_,=20 To: SAS-L@LISTSERV.UGA.EDU=20 Sent: Friday, August 15, 2008 3:51 PM Subject: Re: Deleting SAS Data from a SAS DATASET 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 group of 24. You can see from the notes how the operation works. The data sets don't have to use a numbered range M01-M24 I did that...
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!" -Mary ----- Original Message -----=20 From: Ian Whitlock=20 To: SAS-L@LISTSERV.UGA.EDU=20 Sent: Friday, August 15, 2008 6:07 PM Subject: Re: Deleting SAS Data from a SAS DATASET Summary: Suggestions for maintaining large data set #iw-value=3D1 Chandra, You are adding about 2 gig per month. That is 4% increase. Perhaps accepting the increase one month and then modifying the second month would make more sense, if the data is accessed infrequently. To do this you need to control all programs that read the data set. ...
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; > >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. > >-Mary > ----- Original Message ----- > From: SUBSCRIBE SAS-L Chandra Gadde > To: SAS-L@LISTSERV.UGA.EDU > Sent: Friday, August 15, 2008 12:14 PM > Subject: Deleting SAS Data from a SAS DATASET > > > Hi All > > I have several SAS datasets that are very very big. (50GB of size). Every > month, the data is being appended to these datasets. I need to deleted the > data which is greater than 24 months. What is the best method to do this? > Please help me. > > I tried PROC SQS and DATA STEP. But these two are taking very long time. > > Data prod.Master_data; > ...
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 Reply-To: SUBSCRIBE SAS-L Chandra Gadde <ddraj2015@GMAIL.COM> Sender: "SAS(r) Discussion" From: SUBSCRIBE SAS-L Chandra Gadde <ddraj2015@GMAIL.COM> Subject: Deleting SAS Data from a SAS DATASET Hi All I have several SAS datasets that are very very big. (50GB of size). Every month, the data is being appended to these datasets. I need to deleted the data which is greater than 24 months. What is the best method to do this? Please help me. I tried PROC SQS and DATA STEP. But these two are taking very long time. Data prod.Master_data; set prod.master_date; if snap_dt = "&end_dt"d then delete; run; proc sql; crete table prod.master_date from prod.master_date where snap_dt ne "&end_dt"d; quit; ...
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, There are only 24, not like thousands or millions! :-) and with any DB system, if you have a huge table, then it is more likely that it will map to multiple physical files behind the scene anyway. In terms of querying speed, i rather think it can be faster. One way is to spawn many sas sessions to do multiple separate queries at the same time, then put together... sort of like do-it-yourself parallel processing. implementation is left as homework. happy friday! cheers, chang ...
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@LISTSERV.UGA.EDU=20 Sent: Friday, August 15, 2008 5:03 PM Subject: Re: Deleting SAS Data from a SAS DATASET 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 =3D "one"; output one; ds =3D "two"; output two; run; /* separately index on age */ proc datasets lib=3Dwork; 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 utili...
Re: SAS Macro to delete a SAS dataset #2 313487
I too like(d) PROC DELETE. When I used it I was forced to remove it and use PROC DATASETS because PROC DELETE is not documented. Also, but I have not tested it. How does it work with indexed data sets, audit trails, generation data groups, views, etc.? I don't worry much about deleting, "all" of my programs run in batch and are obsolete as soon as they are written. On 12/3/09, Fehd, Ronald J. (CDC/CCHIS/NCPHI) <rjf2@cdc.gov> wrote: > > From: SAS Techies > > Subject: SAS Macro to delete a SAS dataset > > > > Here's a simple SAS...
Deleting SAS Data from a SAS DATASET
Hi All I have several SAS datasets that are very very big. (50GB of size). Every month, the data is being appended to these datasets. I need to deleted the data which is greater than 24 months. What is the best method to do this? Please help me. I tried PROC SQS and DATA STEP. But these two are taking very long time. Data prod.Master_data; set prod.master_date; if snap_dt = "&end_dt"d then delete; run; proc sql; crete table prod.master_date from prod.master_date where snap_dt ne "&end_dt"d; quit; Hi You could use a 'modify' statement (data step). This statement does the changes in place and needs therefore only have of the space, BUT: there is a risk of corrupting your data; look it up in the manual. I also think that some re-designing could solve some problems and improve performance. I assume you're loading kind of a data mart with a history of 24 months. What you could do (just an idea): - Create a SAS file per month (eg: prod.master_date&yyyymm) - Create a SAS view for the most recent 24 SAS files (prod.master). - Create an index over the date field. Advantages: - Loading of new data will be much faster (create new file with current month, re-create view, delete SAS file with data older 24 months). - As the SAS view has the same name like previously the SAS data file your users won't even get that something changed (assuming they only read the data). - Queries using the index will perform much faster. - SAS power...
Re: What r the data types in SAS? in Base SAS , and SAS SQL
> From: Amar Mundankar > Sent: Tuesday, July 21, 2009 8:10 AM > To: sas-l@uga.edu > Subject: What r the data types in SAS? in Base SAS , and SAS SQL > > Hi all, > What are the different data types in Base SAS and SAS SQL?? character, lengths from 1 to 32,000+ numeric: lengths from 2 to 8 dates are a subtype of numeric and are identified by their date, datetime, or time formats the new proc TSPL (Table Server Processing Language) supports ANSI data types: bigint, tinyint, etc. http://support.sas.com/documentation/cdl/en/tsag/30878/HTML/default/a003 065339.htm http://s...
Re: SAS Macro to delete a SAS dataset 196997
> From: SAS Techies > Subject: SAS Macro to delete a SAS dataset > > Here's a simple SAS Macro to delete a SAS dataset...It takes the name of > the SAS dataset as the parameter... > > %macro deletedsn(dsname); > > %if %index(&dsname,'.') eq 0 %then %do; %let lib=work; %let > dsn=&dsname; %end; > %else %if %index(&dsname,'.') eq 0 %then %do; %let lib=%scan(&dsname, > 1,'.'); %let dsn=%scan(&dsname,1,'.'); %end; > > proc datasets lib=&lib nolist; > delete &dsn; > qui...
Re: search SAS data set from SAS code #2
Rose, The answer to your question depends on your operating system. In Windows, there's the Search tool. In Unix/Linux, you can use grep Bob Abelson HGSI 240 314 4400 x1374 bob_abelson@hgsi.com Rose <myr_rose@YAHOO.COM> Sent by: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU> 04/19/2005 11:13 AM Please respond to myr_rose To: SAS-L@LISTSERV.UGA.EDU cc: Subject: search SAS data set from SAS code Hi All, Suppose I have a sas permanent data set which was created early, I know the library path but I couldn't remember in which s...
Re: Is it possible to read a sas dataset NOT using sas? #2
I think SPSS and DBMSCopy can read SAS datasets. Also for sure WPS. Gerhard On Tue, 18 Mar 2008 06:43:44 -0700, RolandRB <rolandberry@HOTMAIL.COM> wrote: >Is it possible to read a sas dataset NOT using sas and have >observations and variables etc. rather than having meaningless chunks >of text? ...