BookmarkSubscribeRSS Feed
corkee
Calcite | Level 5

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;

 

5 REPLIES 5
PaigeMiller
Diamond | Level 26

@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
corkee
Calcite | Level 5
Hi, yes. I'll go edit that.

Let me try your proposed solution and let you know. Thank you.
corkee
Calcite | Level 5

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

PaigeMiller
Diamond | Level 26

Included in what way? Explain. Give examples.

--
Paige Miller
Astounding
PROC Star

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.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1328 views
  • 0 likes
  • 3 in conversation