Filter data by multiple variables

[ANKH1]

Hi, we need to filter a dataset (dsin). Grouping by each ID, we need to select by session the lowest grade. In cases when there is the same session value and same grade, the session that happened last (the highest numbered month)should be chosen (see dsout).

data dsin,
input id session month grade;
datalines;
1	2	3	4
1	3	4	1
1	4	5	1
2	1	1	4
2	3	3	1
2	3	5	3
2	4	6	3
3	1	2	1
3	1	4	1
3	2	6	1
3	3	10	4
3	4	12	2
;
run;


data dsout,
input id session month grade;
datalines;
1	2	3	4
1	3	4	1
1	4	5	1
2	1	1	4
2	3	3	1
2	4	6	3
3	1	4	1
3	2	6	1
3	3	10	4
3	4	12	2
;
run;

I tried this code but it is not filtering correctly:

proc sort data=dsin;
    by id session grade descending month;
run;

data dsout;
    set dsin;
    by id session grade;
    if first.grade;
run;

I am not sure what I am missing. Any advice is appreciated. Thanks!

[ballardw · Accepted solution]

First, please note that neither of your data steps run because the DATA statement ends with a comma and not a semicolon.

WITHIN Session EACH level of Grade would have a "first.grade".

You are close. You want the first session with that sort:

data work.dsout;
    set work.dsin;
    by id session grade;
    if first.session;
run;

[ANKH1]

thanks!

[PaigeMiller]

First, I don't think your sort is correct. DESCENDING is not needed here.

proc sort data=dsin;
    by id session grade month;
run;

Then the first element for each session is what you need.

data dsout;
    set dsin;
    by id session;
    if first.session;
run;

Note: it is likely to be a poor choice in the real world to represent month via an integer 1 through 12. If the session crosses a year boundary, storing month without the associated year will lead to incorrect results. Try to always use valid SAS dates, which contain year, month and date ... although in this case the date is not needed and can be set to 1.