How to pick out the nonmissing first 2 observation per proc sort by variables: id , ord and test

[purpleclothlady]

hi all:

I tried to use data step or proc sql to find out the /*result*/ any suggestions? thanks in advance.

data test;
infile datalines;
input id ord test $4.;
datalines;
1 0 A

1 0 B
1 1 A
1 1 A
1 1 B
1 3 A
1 3 B
2 0
3 0.2 A
3 0.2 B
3 1 A
3 1 B
3 2 A
3 2 B
3 3 A
3 3 B
3 4 A
4 1 A
4 1 B
run;

/*

desired output look like this:

1 0 A
1 0 B

3 0.2 A
3 0.2 B
4 1 A
4 1 B

*/

[ballardw]

This works for your example data:

data test;
infile datalines missover;
input id ord test $4.;
datalines;
1 0 A
1 0 B
1 1 A
1 1 A
1 1 B
1 3 A
1 3 B
2 0
3 0.2 A
3 0.2 B
3 1 A
3 1 B
3 2 A
3 2 B
3 3 A
3 3 B
3 4 A
4 1 A
4 1 B
run;

data want;
  set test;
  by id ord;
  retain counter;
  if first.id then counter=1;
  else counter+1;
  if first.ord = last.ord then delete;
  else if counter le 2;
  drop counter;
run;

Note that I tweaked your data step code because the missing value for Test causes issues. Suggest posting code in a text box or code box.

---

@purpleclothlady wrote:

hi all:

I tried to use data step or proc sql to find out the /*result*/ any suggestions? thanks in advance.

data test;
infile datalines;
input id ord test $4.;
datalines;
1 0 A

1 0 B
1 1 A
1 1 A
1 1 B
1 3 A
1 3 B
2 0
3 0.2 A
3 0.2 B
3 1 A
3 1 B
3 2 A
3 2 B
3 3 A
3 3 B
3 4 A
4 1 A
4 1 B
run;

/*

desired output look like this:

1 0 A
1 0 B

3 0.2 A
3 0.2 B
4 1 A
4 1 B

*/

---

[Tom]

---

@ballardw wrote:

...

Note that I tweaked your data step code because the missing value for Test causes issues. Suggest posting code in a text box or code box.

...

Not sure that you mean.  It looks like you added the MISSOVER option to the INFILE statement.  That was not needed with in-line data as card images are always fix length (a multiple of 80 bytes).

But even if you did have the data in an external file:

options parmcards=example;
filename example temp;
parmcards;
1 0 A
1 0 B
1 1 A
1 1 A
1 1 B
1 3 A
1 3 B
2 0
3 0.2 A
3 0.2 B
3 1 A
3 1 B
3 2 A
3 2 B
3 3 A
3 3 B
3 4 A
4 1 A
4 1 B
;

You wouldn't want to use the MISSOVER option with the formatted input statement, you would need the TRUNCOVER option.

Check out what happens:

data test2;
  infile example missover;
  input id ord test $4.;
run;

data test3;
  infile example truncover;
  input id ord test $4.;
run;

proc compare data=test2 compare=test3;
run;

You can make the MISSOVER option work if you make sure to always use LIST MODE input statements.  For example by prefixing any in-line informat specification with the colon modifier.

  input id ord test :$4.;

[purpleclothlady]

hi @ballardw:
thanks for the reminder. I appreciate that

[Tom · Accepted solution]

You just need to count the observations within an id.

For example you could nest the SET statement inside a DO loop.

data want;
  do obs=1 by 1 until(last.id);
    set test;
    by id;
    if obs<=2 and not (first.id and last.id) then output;
  end;
  drop obs;
run;

[purpleclothlady]

Hi Tom:
that works .thanks a ton