topic Re: Filter data by multiple variables in SAS Programming

Filter data by multiple variables

ANKH1 — Fri, 07 Jun 2024 14:51:25 GMT

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!

Re: Filter data by multiple variables

ballardw — Fri, 07 Jun 2024 15:05:08 GMT

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;

Re: Filter data by multiple variables

PaigeMiller — Fri, 07 Jun 2024 15:42:17 GMT

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.

Re: Filter data by multiple variables

ANKH1 — Fri, 07 Jun 2024 15:52:32 GMT

thanks!