topic Re: How to delete first five and last five observations from each variable in a dataset in Statistical Procedures

How to delete first five and last five observations from a dataset

mmhxc5 — Fri, 04 Jan 2019 04:34:47 GMT

Hi,

I have a dataset of over 3000 observations for bridges inspected since 1992 -2017. Each bridge is inspected once each year. I would like to delete the first five years and last five years of inspection result for each bridge. Chosing STRUCTURE_NUMBER_008 or Inspection_Year might work as the variable to delete, because the beginning and last year of inspection is not the same for all bridges. The attached image shows two bridge IDs sorted by STRUCTURE_NUMBER_008 (First column) and Inspection_Year(Last column). STRUCTURE_NUMBER_008 is random name given for each bridge. I would be glad if anyone could help me to do this task. I use SAS 9.4 in Windows 10 and I have the dataset as permanent SAS files.

Thanks,

Re: How to delete first five and last five observations from a dataset

novinosrin — Fri, 04 Jan 2019 05:25:35 GMT

data want;
do p=6 to nobs-5;
   set have nobs=nobs point=p;
    output;
end;
stop;
run;

Re: How to delete first five and last five observations from each variable in a dataset

mmhxc5 — Fri, 04 Jan 2019 15:36:11 GMT

Thank you for you response. Your code only removes the first five and last five observation of the whole dataset which is over 3000 observations.

What I want is to remove first five and last five observation for each bridge which has 25 observation each. The dataset of 120 bridges makes the 3000 observations. I only want to retain the middle 15 observation for each bridge. The STRUCTURE _NUMBER_008 is a mix of character and numeric. The dataset is ordered based on the STRUCTURE_NUMBER_008 and Inspection_Year.

Thanks,

Re: How to delete first five and last five observations from each variable in a dataset

novinosrin — Fri, 04 Jan 2019 16:25:32 GMT

Please follow the comments and logic and see if this helps

/*Creating a sample data with 120 bridges with each bridge having 25 observations*/
data sample;
do bridge=1 to 120;
	do var=1 to 25;
	output;
	end;
end;
run;

/*Output */
data want;
/*1st loop count the number of records in each bridge*/
do n=1 by 1 until(last.bridge);
set sample;
by bridge;
end;
/*2nd loop remove first five and last five observation for each bridge 
and output the rest*/
do _n_=1 by 1 until(last.bridge);
set sample;
by bridge;
if 6<=_n_<= n-5 then output;
end;
run;

Re: How to delete first five and last five observations from a dataset

Ksharp — Sat, 05 Jan 2019 11:09:02 GMT

It is forwards and backwards tricks .

data sample;
do bridge=1 to 10;
	do var=1 to 25;
	output;
	end;
end;
run;

data want;
 merge sample sample(firstobs=6 keep=bridge rename=(bridge=_bridge));
 if bridge ne lag5(bridge) or bridge ne _bridge then delete;
 drop _bridge;
 run;

proc print;run;

Re: How to delete first five and last five observations from each variable in a dataset

Rick_SAS — Sat, 05 Jan 2019 13:02:54 GMT

If you are not familiar with @novinosrin's BY-group trick, see "How to use FIRST.variable and LAST.variable in a BY-group analysis in SAS."

Re: How to delete first five and last five observations from each variable in a dataset

mmhxc5 — Mon, 07 Jan 2019 20:53:22 GMT

Thank you for your timely reply. I really appreciate it. Based on your code I created a "Sample" data, then I defined the "want" and also the set "sample" to the end of your code. How can I link this code to the Bridge dataset I have? I mean, which part of your code will call the bridge dataset which has all the data that I want to process?

Re: How to delete first five and last five observations from a dataset

mmhxc5 — Mon, 07 Jan 2019 20:58:26 GMT

Thank you for your timely reply. I really appreciate it. There is only two data calling in your code, "sample" and "want". How can I link this code to the Bridge dataset I have? I mean, which part of your code will call the bridge dataset which has all the data that I want to process?

Re: How to delete first five and last five observations from a dataset

art297 — Tue, 08 Jan 2019 01:36:33 GMT

Both @novinosrin and @Ksharp created a file called sample to represent your bridge dataset. They did not expect you to create a sample dataset but, rather, replace it with your actual bridge dataset.

However, you have to decide whether you want to drop the first and last 5 records for each structure_no_008 OR, for each structure_no_008, you want to drop any inspection_year that was less than the earliest+4 and/or greater than the last inspection_year -4.

My suggested code does the latter:

data bridge (drop=structure_no var);
  do structure_no=1 to 120;
    structure_no_008=catt('000000000000H',put(structure_no,z3.));
    inspection_year=1996;
    do var=1 to 25;
      inspection_year+1;
      if mod(structure_no,40) eq 0 then do;
        if inspection_year ne 2000 then output;
      end;
      else output;
    end;
  end;
run;

/*Output */
data want (drop=start last);
/*1st loop count the number of records in each bridge*/
  do until(last.structure_no_008);
    set bridge;
    by structure_no_008;
    if first.structure_no_008 then start=inspection_year;
    else if last.structure_no_008 then last=inspection_year;
  end;
/*2nd loop only keep inspection_years if they are greater than the
  earliest inspection_year+4 and lower than latest inspection-4*/
  do until(last.structure_no_008);
    set bridge;
    by structure_no_008;
    if start+5 <=inspection_year<=last-5 then output;
  end;
run;

Art, CEO, AnalystFinder.com

Re: How to delete first five and last five observations from a dataset

mmhxc5 — Tue, 08 Jan 2019 04:00:31 GMT

Thank you @novinosrin, @Ksharp, @Rick_SAS, and @art297 for your help. Appreciated. @art297 your clarification was very helpful.

Re: How to delete first five and last five observations from a dataset

Ksharp — Tue, 15 Jan 2019 07:40:53 GMT

‘Sample' dataset is 'Bridge' dataset ,according to Art.T 's code.