Removing records from SAS Dataset

[deleted_user]

Hello,
I have a question involving removing records from a SAS dataset. This link has a made-up dataset similar to what I am working with:

http://www.usaupload.net/d/xkohpqw4f3k

There are many records and results for the months of October, November, and December. The only three sites in the dataset are the ones in the table above, and the same person can be in the set more than once for a different test.

In need to:
Remove the last 300 records from the Health Dept(the ones with the latest dates) from October, the last 300 records from November, and the last 300 from December

Remove the last 800 records from the Hospital Clinic(the ones with the latest dates) from October, the last 800 records from November, and the last 800 from December

Remove the last 600 records from the Doctor Office(the ones with the latest dates) from October, the last 600 records from November, and the last 600 from December

What code would I need to use to get SAS to do this? I would appreciate anyone who would take the time to help me out here. Thanks. Message was edited by: State2000

[deleted_user]

if your data can be put in order by the date on which you base "latest", then something like this is worth trying[pre] proc sort data= your.data out= sorted ;
by that_date ;
run;

data reduced ;
set sorted( firstobs= 301 ) ;
run ;[/pre]
That process will jump over the first 300 records.
A similar process for each data set where you want to ignore the 800 oldest would use[pre] set sorted( firstobs= 801 ) ;[/pre]

good luck

PeterC

[deleted_user]

Hi,

You can do it, this way--create a variable month to have the values of month and then create a counter by month,sort the dataset by descending dates to have latest ones on top,then delete records where counter value is more than 300.Here is the sample code for that.

data a;
set your.data;
month=month(date);
run;

proc sort data=a; by month; run;/*Not required if month values are sorted, in my case I didnt use it*/

data b ;
set a;
by month;
if first.month then cnt=1;
else cnt+1;
run;

proc sort data=b out=c; by descending date;run;

data d;
set c;
if cnt>300 then delete;
run;
run;

Try this it worked for me, hope it does for you as well.

Thanks.

[sbb]

Other replies demonstrate use of pre-sorted data combined with SAS DATA step processing. A BY statement in the DATA step facilitates using IF FIRST.varname processing, however what is not mentioned is the need to include your "site" variable in the SORT and DATA step BY statement, in order to reset the internal counter variable with each new site value.

So, in order to remove "last nnn records" you will need to sort the data by some "date-period" which can be derived using the INTNX function. This date-period variable must already be in your SAS file when it comes to performing the FIRST.varname processing.

So, here's your processing flow:

1) if needed, DATA step to assign a month-start date using INTNX.
2) SORT step to order data by "site" and "date-period" (descending).
3) DATA step to for FIRST. processing to remove unwanted observations, as determined for each unique "site".


Scott Barry
SBBWorks, Inc.