topic Re: delete obs according to percentage specified in SAS Programming

delete obs according to percentage specified

RTelang — Fri, 21 Oct 2016 10:37:58 GMT

Below code is to get the percent of the obs, I hav 300 obs in test dataset i want to delete certain random oberservations according to the percent provided to my code parameter. means if the delete_freq=20 then 20% of 300 ie from the dataset 60 obs should be deleted and only 240 observations in test1 should be remaining.

Am at a fix to populate a condition to delete the obs according to the delete_freq parameter any help is appreciated.

%macro delobs(delete_freq=)
data test;
set key.test1;
run;

proc sort data= test;
by name;
run;

proc freq data=work.test;
tables name / out=counts (KEEP=name count percent);
run;

data all;
merge counts test;
by name;
if count <= &delete_Freq then delete variable_obs;
run;

%mend;
%delobs(delete_freq=12);

Re: delete obs according to percentage specified

RW9 — Fri, 21 Oct 2016 11:20:48 GMT

%macro delobs(delete_freq=)
  data test;
    set key.test1;
  run;
  proc sort data= test;
    by name;
  run;
  proc freq data=work.test;
    tables name / out=counts (KEEP=name count percent);
  run;
  data all;
    merge counts test;
    by name;
    if count > &delete_Freq. then output;
  run;
%mend delobs;
%delobs(delete_freq=12);

Reverse your logic and only output if count > paramter.

Re: delete obs according to percentage specified

Quentin — Fri, 21 Oct 2016 12:34:59 GMT

Do you need to delete exactly 60 obs, or would it be okay to give each record a 20% chance of deletion, which could result in more or less records deleted, depending on chance?

Re: delete obs according to percentage specified

data_null__ — Fri, 21 Oct 2016 12:48:34 GMT

@Quentin I was thinking it would be easier to select 80% of the records using SURVEYSELECT.

Re: delete obs according to percentage specified

Reeza — Fri, 21 Oct 2016 13:04:27 GMT

The datastep in NOT RANDOM. It always get sorted by name and removes all within specified range.

Look into PROC SURVEYSELECT instead.

Re: delete obs according to percentage specified

RTelang — Fri, 21 Oct 2016 13:49:59 GMT

@Quentin i want to randomly delete obs from my data that is in terms of percentage. has mentioned above i hav 300 obs so if my parameter value is 20 so 20% of 300 is 240 so in the output dataset 240 obs should be remaining.

Re: delete obs according to percentage specified

Quentin — Fri, 21 Oct 2016 14:39:43 GMT

Agree with the suggestions from others that this is what PROC SURVEYSELECT was made for. Gone are the days when you might have created a variable with random numbers, sorted, and then selected records yourself.

proc surveyselect 
  data=sashelp.shoes
  method=srs
  n=240
/*  samprate=.8 */
  out=MySample
  seed=0
;
run;

You can specify the count of records to select, or the sampling rate. Of course you could wrap that in a macro, which could compute the count you want from the rate, or ...

Re: delete obs according to percentage specified

RTelang — Fri, 21 Oct 2016 14:53:06 GMT

@Quentin can't my code be updated to randomly delete certain specific percent of observations from the total observations in the dataset... as i am new to surveyselect don't know its working....

Re: delete obs according to percentage specified

ballardw — Fri, 21 Oct 2016 15:50:25 GMT

Your example code does not even look like it was attempting to create a subset of a specific percentage size of the original data. It looks like it was attempting to delete records whose identification variable, Name, appeared fewer than a specified number of times.

So which do you want to do: Select a random subset of a specified number of records or remove very specific identified records that appear with low frequency?

The basic approach you started with will not remove a "random" anything. As a minimum you need a random value somewhere if the goal is actually a random subset. And Surveyselect will not require passing the data through multiple data steps or procedures.

Re: delete obs according to percentage specified

RTelang — Fri, 21 Oct 2016 16:05:37 GMT

@ballarwd I want to delete observations from the dataset based on the delete_freq=20 parameter i.e delete only 20% of the total observations. for example i have 200obs & have value 20 in delete_freq then just delete 20% of total 200 obs i.e any 40 obs will be deleted and only 160 obs will be left...

Re: delete obs according to percentage specified

Reeza — Fri, 21 Oct 2016 15:54:28 GMT

@RTelang wrote:
@Quentin can't my code be updated to randomly delete certain specific percent of observations from the total observations in the dataset... as i am new to surveyselect don't know its working....

I suggest reading the documentation then.

Re: delete obs according to percentage specified

ballardw — Fri, 21 Oct 2016 15:58:43 GMT

@RTelang wrote:
I want to delete observations from the dataset based on the delete_freq=20 parameter i.e delete only 20% of the total observations. for example i have 200obs & have value 20 in delete_freq then just delete 20% of total 200 obs i.e any 40 obs will be deleted and only 160 obs will be left...

@Quentin's reply with Surveyselect code is your best bet.

The SAMPRATE option indicates what percentage of records to select (or keep). A SAMPRATE value less than one such as .8 is keep 80 percent, or if you use a value greater than one it is treated as percentage so Samprate=80 would keep 80 percent of the records.

OR you can actually specifize the number of records to keep with SAMPSIZE if you prefer.

If you insist on using a removal percentage then have your code substract that value from 100 and place that in the SAMPRATE= option.

Re: delete obs according to percentage specified

RTelang — Fri, 21 Oct 2016 16:25:00 GMT

@ballward my criteria is i calculate, want to delete a certain percent of obs values from the DS observations.. dont want to keep the specific number of values i.e i want to directly calculate 20% of 300 that is 40 & then delete 40 obs from the total 300 obs tats it....

Re: delete obs according to percentage specified

Quentin — Fri, 21 Oct 2016 16:55:22 GMT

That is what surveryselect is doing. You specify how many records (or what proportion) to select, rather than delete.

If you really want to do it with data step code, this page presents surveyselect and two data step options.

http://support.sas.com/kb/24/722.html

Re: delete obs according to percentage specified

Reeza — Fri, 21 Oct 2016 17:17:09 GMT

@RTelang wrote:
@ballward my criteria is i calculate, want to delete a certain percent of obs values from the DS observations.. dont want to keep the specific number of values i.e i want to directly calculate 20% of 300 that is 40 & then delete 40 obs from the total 300 obs tats it....

That's like saying 2 + 3 is different from 3 + 2.

Re: delete obs according to percentage specified

SuryaKiran — Fri, 21 Oct 2016 20:19:51 GMT

Just a simple code that output depending on percentage.

proc sql;

select count(*) Into: OBSCOUNT

from sashelp.class;

quit;

%let pct=80 ; /* Percentage of observation to output */

data test1;

set sashelp.class ;

if _N_=INT((&pct*&obscount)/100)+1 then stop;

run;

Re: delete obs according to percentage specified

Reeza — Fri, 21 Oct 2016 20:24:53 GMT

@SuryaKiran That's not a random selection - it selects the top N records.

Re: delete obs according to percentage specified

Ksharp — Sat, 22 Oct 2016 04:04:05 GMT

Yeah. @data_null__ ,John King got the point.

If you really want DELETE.

%let delete_freq=12;

proc surveyselect data=sashelp.cars group=100 out=want;
run;
data want;
 set want;
 if groupid le &delete_freq then delete;
 drop groupid;
run;

Re: delete obs according to percentage specified

RTelang — Wed, 26 Oct 2016 06:54:25 GMT

@Ksharp lil brief explanation of code wats it doing...?

Re: delete obs according to percentage specified

RW9 — Wed, 26 Oct 2016 08:17:34 GMT

There is a thing called Base SAS - it is the programming language your are using, even if you insist on wrapping the whole system in macro code. It is a well documented fully functional languange, and you can find all the documentation online at SAS:

https://support.sas.com/documentation/cdl/en/statug/63033/HTML/default/viewer.htm#surveyselect_toc.htm