DATA Step, Macro, Functions and more

Selecting rows with absolute minimum within each group

Reply
Contributor
Posts: 29

Selecting rows with absolute minimum within each group

[ Edited ]

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;

 

Respected Advisor
Posts: 2,976

Re: Selecting rows with absolute minimum within each group


@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;
--
Paige Miller
Contributor
Posts: 29

Re: Selecting rows with absolute minimum within each group

Posted in reply to PaigeMiller
Hi, yes. I'll go edit that.

Let me try your proposed solution and let you know. Thank you.
Contributor
Posts: 29

Re: Selecting rows with absolute minimum within each group

Posted in reply to PaigeMiller

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

Respected Advisor
Posts: 2,976

Re: Selecting rows with absolute minimum within each group

Included in what way? Explain. Give examples.

--
Paige Miller
Super User
Posts: 6,751

Re: Selecting rows with absolute minimum within each group

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.

Ask a Question
Discussion stats
  • 5 replies
  • 121 views
  • 0 likes
  • 3 in conversation