topic Re: Selecting rows with absolute minimum within each group in SAS Programming

Selecting rows with absolute minimum within each group

corkee — Wed, 04 Apr 2018 22:15:46 GMT

Hi,

I have a dataset that's structured like the following:

participant_id     v1datediff
1                         -22
1                         10
1                         -2
2                         1
2                         3
3                         2

I want to only extract rows where the absolute value of v1datediff is the minimum for each individual (i.e., I want rows 3, 4, and 6). I've merged two datasets together and have attempted the following code, but to no avail. Any suggestions would be appreciated.

proc sql;
	create table visit1_merged as
	select *,
	v1completedate-date as v1datediff
		from
			visit1_want
			full join
			ehr_data
			on visit1_want.chldmrn=ehr_data.chldmrn
	;
quit;


proc sql;
	create table v1final as
	select *
	from visit1_merged
	group by participant_id
	having v1datediff = abs(min(v1datediff))
	;
quit;

Re: Selecting rows with absolute minimum within each group

PaigeMiller — Wed, 04 Apr 2018 22:09:25 GMT

@corkee wrote:

Hi,

I have a dataset that's structured like the following:
participant_id     v1datediff
1                         -22
1                         10
1                         -2
2                         1
2                         3
3                         2
I want to only extract rows where the absolute value of v1datediff is the minimum for each individual (i.e., I want rows 3, 5, and 6).

Should that say "Rows 3, 4 and 6"????

PROC SUMMARY will tell you the minimum value of v1datediff, provided you take the absolute value in data step prior to PROC SUMMARY.

So assuming you have so type of ID in the data set (which could simply be rownumber), this ought to work

data have;
    set have;
    absv1datediff=abs(v1datediff);
    rownumber = _n_;
run;
proc summary data=have nway idmin;
    class participant_id;
    var absv1datediff;
    id rownumber;
    output out=minimums min=min_v1datediff;
run;

Re: Selecting rows with absolute minimum within each group

corkee — Wed, 04 Apr 2018 22:15:10 GMT

Hi, yes. I'll go edit that.

Let me try your proposed solution and let you know. Thank you.

Re: Selecting rows with absolute minimum within each group

corkee — Wed, 04 Apr 2018 22:19:46 GMT

Could this solution be modified in a way that all other columns in the dataset are also included?

Re: Selecting rows with absolute minimum within each group

PaigeMiller — Wed, 04 Apr 2018 23:08:55 GMT

Included in what way? Explain. Give examples.

Re: Selecting rows with absolute minimum within each group

Astounding — Thu, 05 Apr 2018 03:43:45 GMT

I can't test this right now, but I expect that your original code is very close. A slight modification to the HAVING clause:

having abs(v1datediff) = min(abs(v1datediff));

You might get multiple rows for an individual, if there are ties for the minimum value.