Selecting rows with absolute minimum within each group

[corkee]

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;

[PaigeMiller]

---

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

[corkee]

Hi, yes. I'll go edit that.

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

[corkee]

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

[PaigeMiller]

Included in what way? Explain. Give examples.

[Astounding]

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.