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

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

purpleclothlady — Wed, 17 Nov 2021 14:29:07 GMT

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

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

ballardw — Wed, 17 Nov 2021 15:40:56 GMT

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

*/

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

Tom — Wed, 17 Nov 2021 16:01:25 GMT

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;

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

Tom — Wed, 17 Nov 2021 16:07:27 GMT

@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.;

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

purpleclothlady — Thu, 18 Nov 2021 19:01:21 GMT

Hi Tom:
that works .thanks a ton

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

purpleclothlady — Thu, 18 Nov 2021 19:02:10 GMT

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