BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.

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

*/

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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;

View solution in original post

5 REPLIES 5
ballardw
Super User

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
Super User Tom
Super User

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

 

Tom
Super User Tom
Super User

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;

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 1317 views
  • 1 like
  • 3 in conversation