PLZ: Macro Urgent Help

[1234567]

data one;
input FPR c1 c2 TPR;
cards;
0.1 1.63223 1.63223 0.81557
0.2 1.25044 1.25044 0.91712
0.3 0.98085 0.98085 0.95858
0.4 0.75412 0.75412 0.97883
0.5 0.54504 0.54504 0.98940
0.6 0.33854 0.33854 0.99502
0.7 0.12029 0.12029 0.99793
0.8 -0.13165 -0.13165 0.99932
0.9 -0.47569 -0.47569 0.99988
1.0 -1.27726 -1.27726 1.00000
;
proc means data=one max ;
var TPR;
output out=maxMTPR max=MTPR;
by FPR;
run;

I want to create one table contain data that has the maximum of TPR (MTPR) for every level of FPR that less than threshold, such that
FPR <= 0.1
FPR <= 0.2
FPR <= 0.3
FPR <= 0.4
FPR <= 0.5
FPR <= 0.6
FPR <= 0.7
FPR <= 0.8
FPR <= 0.9
FPR <= 1
And every run output the c1, c2 correspond to the maximum of TPR (MTPR). Can you provide me macro program to do this.
Thank you for help

[1234567]

, such that
FPR

FPR <= 0.1
FPR <= 0.2
FPR <= 0.3
FPR <= 0.4
FPR <= 0.5
FPR <= 0.6
FPR <= 0.7
FPR <= 0.8
FPR <= 0.9
FPR <= 1
And every run output the c1, c2 correspond to the maximum of TPR (MTPR). Can you provide me macro program to do this.
Thank you for help

[1234567]

, such that
FPR
FPR LE 0.1
FPR LE 0.2
FPR LE 0.3
FPR LE 0.4
FPR LE 0.5
FPR LE 0.6
FPR LE 0.7
FPR LE 0.8
FPR LE 0.9
FPR LE 1
And every run output the c1, c2 correspond to the maximum of TPR (MTPR). Can you provide me a macro program to do this.
Thank you for help

[ChrisNZ]

That a job for multilabel formats.
[pre]
proc format;
value lessthan (multilabel)
0-.1 = '<.1'
0-.2 = '<.2'
0-.3 = '<.3'
0-.4 = '<.4'
0-.5 = '<.5'
0-.6 = '<.6'
0-.7 = '<.7'
0-.8 = '<.8'
0-.9 = '<.9'
0-1 = '<1';
run;
proc means data=one max ;
class FPR;
var TPR;
output out=maxMTPR max=MTPR;
format TPR lessthan.;
run;

[ChrisNZ]

Is the size of a post limited now?

You can then merge maxMTPR with the original table to get C1 and C2.

[1234567]

Thank you Chris,

The size of post fine, but am new here and this my first post.

About the SAS code, I like your solution and for sure will be using it. But in case of large data with FPR take small increment (0.01, 0.02, 0.03,0.04,0.05...,0.10,……....,1) the use of format will be impractical. ANY HELP 🙂

[ChrisNZ]

The format will still be the easiest way in my opinion, even for thousands of buckets. In that case, generate the format automatically from a table using the CNTLIN= option. Don't forget to set the HLO variable to 'M'.

[DanielSantos]

If I've understand correctly your question and being the format solution a cleaner and simpler way of doing this, also consider the following alternative.

proc sort data = one;
by FPR;
run;

data output;
set one;
retain _TPR . _C1 _C2; /* temporary vars for holding the maximum reg */
drop _:;
by FPR;
if _TPR lt TPR then do;
_TPR=TPR; _C1=C1; _C2=C2; /* hold maximum reg */
end;
if last.FPR; /* if level end, output */
TPR=_TPR; C1=_C1; C2=_C2;
run;

Anyway, there is no need for macro coding here.

Cheers from Portugal.

Daniel Santos @ www.cgd.pt.

[ChrisNZ]

Indeed, sorting is also an option, though one that should be used after having considered the impact on resources, so I am always wary of recommending it.

In this case, it might be a good idea as we can avoid a subsequent merge. Because we don't care about the full sorted table, I wouldn't create it, but create a view instead (to lower I/O). Also sorting can be used to find the second variable's maximum too.

proc sql;
create view V as
select * from ONE
order by FPR, TPR;

data OUTPUT;
set V;
by FPR;
if last.FPR;
run;

should do the job.

[1234567]

Chris and Daniel Santos thank you both for your input and help. I decided to use the format program, it seems easy for me. Chris, I created sas file that has format of FPR from (0, 0-.01,0-0.02,0-.03,….,0-1), the question is can I load the format file using libname without opining the file. Let say the file in C:/format
Thanks..

[ChrisNZ]

Look up the FMTSEARCH option.

[DanielSantos]

Hello Chris.

I agree with you, sorting is indeed resource consuming.
Creating a view may reduce I/O at creation, but when solving it, the amount of I/O will be about the same or greater (since your sorting by two variables, and for what I've seen, SQL is so much more resource consuming when dealing with very large datasets).
Sorting by FPR will suffice (and so, reduce I/O) , since the maximum TPR is easily obtained during the datasetp pass.

Format is indeed a very good approach, and would be my choice also. But, should the values of C1 and C2 be the same of the row with the maximum TPR?

Cheers from Portugal.

Daniel Santos @ www.cgd.pt.

[1234567]

Thank you Daniel, PROC means can be used to get maximum TPR; and c1 and c2 associated with the maximum TPR .

maxid(TPR(c1))=c1,
maxid(TPR(c2))=c2,

[DanielSantos]

> Thank you Daniel, PROC means can be used to get
> maximum TPR; and c1 and c2 associated with the
> maximum TPR .
>
> maxid(TPR(c1))=c1,
> maxid(TPR(c2))=c2,

OK then, problem solved. Not part of the proc means above.

Cheers from Portugal.

Daniel Santos @ www.cgd.pt

[ChrisNZ]

Good thinking!
And I forgot the mlf option in my proc means, but you knew that.