Skipping every 3rd line when _n_ is given to us

[athapa1183]

Dear All, 

 I am trying to solve this problem . This is how far i have done. the row numbers are given to us and we want to skip to the 3rd line after the given row.

data a1;

infile datalines;
input x name $ ;
datalines;
1 aa
2 bb
3 cc
4 dd
5 ee
6 ff
7 gg
8 hh
9 ii
10 jj
11 kk
12 ll
13 mm
14 nn
15 oo
16 pp
;

the given row numbers are 2, 8 , 10. so when these row numbers are given. I want to skip to the 3rd line. For example when _n_= 2 i want to skip to the 3rd line under it and pick x= 5 & name = ee 

here _n_= 8 is given so skip to 11th line and pick x=11 & name = kk

Output i want is as under 

x   name 

5    ee

11  kk

13  mm

my code which is incomplete as under. 

%let list = 2, 8, 10; /*  The list may have hundreds of numbers, here i only chose 3 */

data test;
set a1;
if _N_ in (&list.)then do ;

I don't know how to skip to 3rd line from here

end;
run;

I appreciate your help in this regard.

Thank You

[gamotte]

A better solution will probably be given but this should work

%macro foo;
	%let list = 2 8 10; /*  The list may have hundreds of numbers, here i only chose 3 */

	%let exclude=;
	%do i=1 %to %sysfunc(countw(&list.));
		%let n=%scan(&list.,&i.,' ');
		%let exclude=&exclude %eval(&n.+1) %eval(&n.+2);
	%end;

	%put &exclude;

	data test;
		set a1;

		if _N_ not in (&exclude.);
	run;
%mend foo;

%foo;

[gamotte]

It does not produce the output you want but i dont understant how exactly it should work.

If, from 8 you skip to 11, you should not take the 10 in your list into account and thus

keep the 12th row.

On the other hand, if you take the 10 into account, the 11th observation should not be in the output.

[athapa1183]

Thank you for helping out. Sorry this was just the sample. yes there will be at least 20 rows under each number. They will not overlap as my example. 

[Astounding · Accepted solution]

You would be responsible for making sure the numbers are legitimate.  For example, you couldn't have 15 in the list, when there are only 16 observations to choose from.

%let list=2, 8, 10;

data want;

do i=&list;

   rownum = i+3;

   set a1 point=rownum;

   output;

end;

stop;

drop i;

run;

It's probably easier if you forget about a macro variable, and keep the row numbers in a SAS data set.  If it makes sense to you, we can explore that in more detail.

[athapa1183]

Thank you. Yes the numbers are not this close they are apart by more than 20 rows.

The last number still will have at least 20 rows under it.

Thank you so much the Code works perfectly fine.

 Can you please explain why it is easy not to use a macro variable. I have 452 such numbers in the list . the total rows are 495,955 and the numbers in the list range from 302,000 to 494,918(last number in the list).

[Astounding]

Macro variables can be used (obviously, I guess).  But they can become lengthy, and you have to make sure that numbers greater than 999 don't contain any commas. 

Look how easy it is to do if you have a data set such as:

data rows;

input rownum;

datalines;

2

8

10

;

Now retrieving the observations is easy:

data want;

set rows;

rownum = rownum + 3;

set a1 point=rownum;

run;

With 452 numbers, or with 45,200 numbers the final DATA step doesn't need to change.

[athapa1183]

UNDERSTOOD. Thank you it really Helps.

Have a nice day.

[SuryaKiran]

If you have 445 number it will be hard to put all those numbers in the list. If you can create a dataset for the numbers you want It is more efficient using HASH Tables. Take the 445 numbers dataset in-memory and match merge using HASH Objects.

FYI: http://support.sas.com/resources/papers/proceedings09/071-2009.pdf

[athapa1183]

I'm looking into it. Thank you.