topic Re: Flag when variable is closer to one in SAS Programming

Flag when variable is closer to one

Miracle — Thu, 23 Jul 2020 10:45:09 GMT

Dear All,

How do I flag=Y when varX is closet to one by id and varZ please?

Thanks in advance.

Re: Flag when variable is closer to one

PeterClemmensen — Thu, 23 Jul 2020 10:48:42 GMT

Show us your data 🙂

Re: Flag when variable is closer to one

PaigeMiller — Thu, 23 Jul 2020 11:07:56 GMT

@Miracle wrote:

How do I flag=Y when varX is closet to one by id and varZ please?

Please explain this further. Provide more details. Show us an example.

Re: Flag when variable is closer to one

Miracle — Thu, 23 Jul 2020 11:22:36 GMT

Thanks @PeterClemmensen and @PaigeMiller for your quick response.
Take this data for example please.
So I need to flag=Y when varX is closest to one by id and varZ.
Thanks in advance.

id	varX	varZ	flag
1	1	A	Y
1	50	A
1	1	B	Y
1	4	B
1	3	C
1	2	C	Y
2	10	A
2	3	A	Y
2	3	A	Y
2	3	B
2	4	B
2	-1	B	Y
2	3	C	Y
2	6	C

Re: Flag when variable is closer to one

PaigeMiller — Thu, 23 Jul 2020 11:31:07 GMT

data two;
     set one;
     distance = abs(varX - 1);
run;
proc summary data=two nway;
     class id varz;
     var distance;
    output out=_stats_ min=min_distance;
run;
data want;
     merge two _stats_;
     by id varz;
     if distance=min_distance than flag="Y";
run;

Re: Flag when variable is closer to one

PeterClemmensen — Thu, 23 Jul 2020 12:31:24 GMT

My 2 cents.

Is there a reason why the row

should not be flagged?

data have;
input id varX varZ $;
datalines;
1 1  A
1 50 A
1 1  B
1 4  B
1 3  C
1 2  C
2 10 A
2 3  A
2 3  A
2 3  B
2 4  B
2 -1 B
2 3  C
2 6  C
;


data want(drop=mdist);
   do _N_ = 1 by 1 until (last.varZ);
      set have;
      by id varZ;
      mdist = min(mdist, abs(1 - varX));
   end;

   do _N_ = 1 to _N_;
      set have;
      flag = ifc(abs(1 - varX) = mdist, "Y", "");
      output;
   end;
run;

Result

id varX varZ flag 
1  1    A    Y 
1  50   A      
1  1    B    Y 
1  4    B      
1  3    C      
1  2    C    Y 
2  10   A      
2  3    A    Y 
2  3    A    Y 
2  3    B    Y 
2  4    B      
2  -1   B    Y 
2  3    C    Y 
2  6    C

Re: Flag when variable is closer to one

gamotte — Thu, 23 Jul 2020 14:35:11 GMT

Hello,

A solution with proc sql :

proc sql noprint;
    CREATE TABLE want AS
    SELECT *, CASE WHEN abs(varX-1)=min(abs(varX-1)) THEN "Y" END AS Flag
    FROM have
    GROUP BY id, varZ
    ;
quit;

Re: Flag when variable is closer to one

Miracle — Mon, 27 Jul 2020 06:18:29 GMT

Thanks @PeterClemmensen for the sharp eye.
You are absolutely right!

Re: Flag when variable is closer to one

Miracle — Mon, 27 Jul 2020 06:32:35 GMT

Thanks @PaigeMiller, @PeterClemmensen and @gamotte for all your solutions! Much appreciate it 😀