DATA Step, Macro, Functions and more

One row needs to be selected

Reply
Occasional Contributor
Posts: 14

One row needs to be selected

I have a dataset like this:

data hello;
input id var1 var2;
datalines;
1 3 5
1 3 7
2 2 4
2 2 7
2 2 9
3 4 6
3 4 9
;
run;

I want to output only one row where the difference beetween var1 and var2 is lowest for the particular id.

Any help.
Super Contributor
Super Contributor
Posts: 3,174

Re: One row needs to be selected

Consider PROC SQL with a sub-query (to get the minimum) or a DATA step using a MERGE / BY statement technique, while using a PROC SUMMARY to get the minimum value for your BY group.

Scott Barry
SBBWorks, Inc.
Frequent Contributor
Posts: 120

Re: One row needs to be selected

Something like this???


data one/view=one;
set hello;
length diff 8;
diff = var1 - var2;
run;

proc sort data = one out = two;
by id diff;
run;

data three;
set two;
by id diff;
if first.id then output;
run;
Valued Guide
Posts: 2,175

Re: One row needs to be selected

> Something like this???
>
>
> data one/view=one;
> set hello;
> length diff 8;
> diff = var1 - var2;
> un;
>
> proc sort data = one out = two;
> by id diff;
> un;
>
> data three;
> set two;
> by id diff;
> if first.id then output;
> un;

good approach and allowing ID groups.
May need refinement to ensure dif is only positive.

The first refinement[pre]> diff = abs( var1 - var2) ;[/pre]

peterC
Ask a Question
Discussion stats
  • 3 replies
  • 111 views
  • 0 likes
  • 4 in conversation