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
*/
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;
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 A1 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 B3 0.2 A
3 0.2 B
4 1 A
4 1 B*/
@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.;
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;
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.