BookmarkSubscribeRSS Feed
1234567
Calcite | Level 5
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
20 REPLIES 20
1234567
Calcite | Level 5
, 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
Calcite | Level 5
, 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
Tourmaline | Level 20
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
Tourmaline | Level 20
Is the size of a post limited now?

You can then merge maxMTPR with the original table to get C1 and C2.
1234567
Calcite | Level 5
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
Tourmaline | Level 20
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
Barite | Level 11
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
Tourmaline | Level 20
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
Calcite | Level 5
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
Tourmaline | Level 20
DanielSantos
Barite | Level 11
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
Calcite | Level 5
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
Barite | Level 11
> 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
Tourmaline | Level 20
Good thinking!
And I forgot the mlf option in my proc means, but you knew that.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 20 replies
  • 1294 views
  • 0 likes
  • 4 in conversation