topic Re: PLZ: Macro Urgent Help in SAS Programming

PLZ: Macro Urgent Help

1234567 — Thu, 02 Jul 2009 13:48:18 GMT

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

Re: PLZ: Macro Urgent Help

1234567 — Thu, 02 Jul 2009 13:50:31 GMT

, 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

Re: PLZ: Macro Urgent Help

1234567 — Thu, 02 Jul 2009 14:00:13 GMT

, 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

Re: PLZ: Macro Urgent Help

ChrisNZ — Fri, 03 Jul 2009 01:12:34 GMT

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;

Re: PLZ: Macro Urgent Help

ChrisNZ — Fri, 03 Jul 2009 01:17:22 GMT

Is the size of a post limited now?

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

Re: PLZ: Macro Urgent Help

1234567 — Fri, 03 Jul 2009 02:50:19 GMT

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 🙂

Re: PLZ: Macro Urgent Help

ChrisNZ — Fri, 03 Jul 2009 02:58:49 GMT

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'.

Re: PLZ: Macro Urgent Help

DanielSantos — Fri, 03 Jul 2009 09:20:44 GMT

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.

Re: PLZ: Macro Urgent Help

ChrisNZ — Sun, 05 Jul 2009 22:12:26 GMT

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.

Re: PLZ: Macro Urgent Help

1234567 — Sun, 05 Jul 2009 22:37:46 GMT

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..

Re: PLZ: Macro Urgent Help

ChrisNZ — Sun, 05 Jul 2009 23:18:02 GMT

Look up the FMTSEARCH option.

Re: PLZ: Macro Urgent Help

DanielSantos — Mon, 06 Jul 2009 09:32:28 GMT

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.

Re: PLZ: Macro Urgent Help

1234567 — Mon, 06 Jul 2009 14:50:27 GMT

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,

Re: PLZ: Macro Urgent Help

DanielSantos — Mon, 06 Jul 2009 16:10:37 GMT

> 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

Re: PLZ: Macro Urgent Help

ChrisNZ — Tue, 07 Jul 2009 01:21:39 GMT

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

Re: PLZ: Macro Urgent Help

ChrisNZ — Tue, 07 Jul 2009 22:09:31 GMT

Daniel,
Re. views, you've piqued my interest. I'll start a new thread.

Re: PLZ: Macro Urgent Help

ChrisNZ — Wed, 08 Jul 2009 03:28:54 GMT

Probably not worth starting a new thread, though this is bit off-topic.

I've conducted a few tests.

[pre]data SASUSER.C;
do I=1 to 2e7;
A=ranuni(0);
B=round(A,.1);
C1=B; C2=B;
output;
end;
run;

%let s=%sysfunc(time());
signon task1 sascmd="!sascmd";
rsubmit task1 wait=no;
libname OUTLIB sasesock ":irc" timeout=5000;
proc sort data=SASUSER.C out=OUTLIB.C1;
by B A;
run;
endrsubmit;
rdisplay task1;
signon task2 sascmd="!sascmd";
rsubmit task2 wait=no;
libname INLIB sasesock ":irc" timeout=5000;
data D1;
set INLIB.C1;
by B;
if last.B;
run;
endrsubmit;
rdisplay task2;
waitfor task2;
%put time=%sysevalf(%sysfunc(time())-&s); ***** MP CONNECT: 130s median time for 3 runs;

proc sort data=SASUSER.C out=C1;
by B ;
data D;
retain _A _C1 _C2;
drop _:;
set C1;
by B;
if _A lt A then do;
_A=A; _C1=C1; _C2=C2;
end;
if last.B;
A=_A; C1=_C1; C2=_C2;
run; ******** SORT TO TABLE: 140s median time for 3 runs;

proc sql;
create view V as select * from SASUSER.C order by B, A;
data D1;
set V;
by B;
if last.B;
run; ******** SORT TO VIEW: 150s median time for 3 runs;
[/pre]

If I make the table wider (C2 as $50), the time differences get larger:
MPC 185s
TABLE 350s
VIEW 450s

So the view is indeed slower, probably due to using SQL rather than proc sort, as you mention.
I'd still use that syntax for small tables as it is a lot easier to read.

The most difficult code to read is MPC, but it is also the fastest, saving on I/O. I hope sas will provide local multithreaded syntax soon to do this kind of processing, rather than having to remote submit. It would make the code a lot cleaner.

Lastly, in this case we can also use
[pre]
proc summary data=SASUSER.C nway noprint ;
class B;
var A;
output out=t max= maxid(A(c1))=c1 maxid(A(c2))=c2;
run;
[/pre]
which runs in a mere 23s for the wide table, so is by very far the most efficient.

Re: PLZ: Macro Urgent Help

DanielSantos — Thu, 09 Jul 2009 11:25:38 GMT

Hello Chris.

Very good analysis the one you provided.

Although very off the original topic, this is getting very interesting.
I find it very nice and valuable to have some more academic discussion here.

I've ran your tests (3 runs) on my machine (AIX box with 12 lcpu and 20GB of RAM) and added 4 more variants to the solution (code posted at the end):

+ Summary Hash technique.
+ SORT(one var only)+DATASTEP with Piping Parallelism
+ MP(4 parts) SORT(one var only)+DATASTEP with Piping Parallelism
+ MP(4 parts) SORT(one var only)+DATASTEP with no Piping Parallelism

Results for the same data of your trials:

SORT(1VAR)+STEP time=32s, 32s, 32s
SQL(2VAR)+STEP time=58s, 57s, 60s
PIPED SORT(2VAR)+STEP time=31s, 30s, 31s
SUMMARY time=5s, 5s, 5s

PIPED SORT(1VAR)+STEP time=26s, 27s, 27s
PIPED/MP 4xSORT(1VAR)+STEP time=24s, 25s, 24s
HASH SUM time=29s, 30s, 32s
MP 4xSORT(1VAR)+STEP time=23s, 25s, 24s

Conclusion.
SQL worst.
Hash not brilliant but, keeps up with the average.
Piped Sort by 1 var does a better job than by 2.
MP(4 parts) good, but pretty much the same with or without piping.
And the gold medal goes for Summary by far.

Now, beside the amount of data, two more factors should be considered.

Type of data - the generated sample produces only 7 distinct values for B.
System load - some techniques (MP/piping and MP+piping) could be greatly influenced by the system load at execution.

So I slightly modified your sample to produce about 1000 distinct values for B.
Then I ran the same tests (3 runs), and here are the results:

SORT(1VAR)+STEP time=59s, 40s, 41s
SQL(2VAR)+STEP time=96s, 68s, 89s
PIPED SORT(2VAR)+STEP time=215s, 39s, 39s
SUMMARY time=63s, 59s, 70s

PIPED SORT(1VAR)+STEP time=45s, 44s, 49s
PIPED/MP 4xSORT(1VAR)+STEP time=37s, 37s, 68s
HASH SUM time=49s, 38s, 36s
MP 4xSORT(1VAR)+STEP time=32s, 37s, 177s

Conclusion.
SQL still worst.
Hash not brilliant but, again keeps up with the average.
MP/Piping processing produces the best marks, but are highly dependent on the system load, and so less stable in performance.
Summary has a lot of more to handle, and goes down on the ranking, very near to SQL performance.

So about summing data with SAS, I would conclude by this results, that there is no particular recommended technique. Still, avoid SQL unless for small amount of data.
Hash, not being the best, will assure you a good performance, but careful with the memory limitations (roughly I would say a limit of 2,000,000 of distinct elements would be reasonable).
Then we have PROC SUMMARY/MEANS which on v9 with its threading support as got a lot better, but, be aware that performance will dramatically reduce with the increase of data, and particularly with the number of distinct elements your grouping.
Multi-Processing with or without Piping Parallism, pushes away your processing power, but are alway very dependent on your system load, and results may vary from best to worst.

Chris, thank you for pushing this subject a little further, I find it great and mentally challenging to have from time to time this kind of discussion.

Cheers from Portugal.

Daniel Santos @ www.cgd.pt.

Re: PLZ: Macro Urgent Help

DanielSantos — Thu, 09 Jul 2009 11:31:31 GMT

Now the code.

Hash Sum:

data _null_;
declare hash HT(hashexp: 16);
HT.defineKey('B');
HT.defineData('B', 'A_MAX', 'C1_MAX', 'C2_MAX');
HT.defineDone();
do until (_EOF);
set LIB.C end = _EOF;
if HT.find() or A gt A_MAX then do;
A_MAX=A; C1_MAX=C1; C2_MAX=C2;
end;
HT.replace();
end;
_RC=HT.output(dataset: 'LIB.D4');
run;

Proc Sort (by one var) and datastep with piping parallelism:

signon task1 sascmd="!sascmd";
rsubmit task1 wait=no;
libname LIB '/batch/sas/SASTMP';
libname OUTLIB sasesock ":9981" timeout=5000;
proc sort data=LIB.C out=OUTLIB.C5;
by B;
run;
endrsubmit;
signon task2 sascmd="!sascmd";
rsubmit task2 wait=no;
libname LIB '/batch/sas/SASTMP';
libname INLIB sasesock ":9981" timeout=5000;
data LIB.D5;
retain _A _C1 _C2;
drop _:;
set INLIB.C5;
by B;
if _A lt A then do;
_A=A; _C1=C1; _C2=C2;
end;
if last.B;
A=_A; C1=_C1; C2=_C2;
run;
endrsubmit;
waitfor task2;
rget task1;
rget task2;

MP (4 parts) with Proc Sort (by one var) and datastep with piping parallelism:

signon task1_1 sascmd="!sascmd";
rsubmit task1_1 wait=no;
libname LIB '/batch/sas/SASTMP';
libname OUTLIB sasesock ":9971" timeout=5000;
proc sort data=LIB.C (firstobs=1 obs=5000000) out=OUTLIB.C7_1;
by B;
run;
endrsubmit;
signon task1_2 sascmd="!sascmd";
rsubmit task1_2 wait=no;
libname LIB '/batch/sas/SASTMP';
libname OUTLIB sasesock ":9972" timeout=5000;
proc sort data=LIB.C (firstobs=5000001 obs=10000000) out=OUTLIB.C7_2;
by B;
run;
endrsubmit;
signon task1_3 sascmd="!sascmd";
rsubmit task1_3 wait=no;
libname LIB '/batch/sas/SASTMP';
libname OUTLIB sasesock ":9973" timeout=5000;
proc sort data=LIB.C (firstobs=10000001 obs=15000000) out=OUTLIB.C7_3;
by B;
run;
endrsubmit;
signon task1_4 sascmd="!sascmd";
rsubmit task1_4 wait=no;
libname LIB '/batch/sas/SASTMP';
libname OUTLIB sasesock ":9974" timeout=5000;
proc sort data=LIB.C (firstobs=15000001 obs=20000000) out=OUTLIB.C7_4;
by B;
run;
endrsubmit;
signon task2 sascmd="!sascmd";
rsubmit task2 wait=no;
libname LIB '/batch/sas/SASTMP';
libname INLIB1 sasesock ":9971" timeout=5000;
libname INLIB2 sasesock ":9972" timeout=5000;
libname INLIB3 sasesock ":9973" timeout=5000;
libname INLIB4 sasesock ":9974" timeout=5000;
data LIB.D5;
retain _A _C1 _C2;
drop _:;
set INLIB1.C7_1 INLIB2.C7_2 INLIB3.C7_3 INLIB4.C7_4;
by B;
if _A lt A then do;
_A=A; _C1=C1; _C2=C2;
end;
if last.B;
A=_A; C1=_C1; C2=_C2;
run;
endrsubmit;
waitfor task2;
rget task1_1;
rget task1_2;
rget task1_3;
rget task1_4;
rget task2;

Finally, MP (4 Parts) Proc Sort (by one var) and datastep without piping parallelism:

signon task1_1 sascmd="!sascmd";
rsubmit task1_1 wait=no;
libname LIB '/batch/sas/SASTMP';
proc sort data=LIB.C (firstobs=1 obs=5000000) out=LIB.C7_1;
by B;
run;
endrsubmit;
signon task1_2 sascmd="!sascmd";
rsubmit task1_2 wait=no;
libname LIB '/batch/sas/SASTMP';
proc sort data=LIB.C (firstobs=5000001 obs=10000000) out=LIB.C7_2;
by B;
run;
endrsubmit;
signon task1_3 sascmd="!sascmd";
rsubmit task1_3 wait=no;
libname LIB '/batch/sas/SASTMP';
proc sort data=LIB.C (firstobs=10000001 obs=15000000) out=LIB.C7_3;
by B;
run;
endrsubmit;
signon task1_4 sascmd="!sascmd";
rsubmit task1_4 wait=no;
libname LIB '/batch/sas/SASTMP';
proc sort data=LIB.C (firstobs=15000001 obs=20000000) out=LIB.C7_4;
by B;
run;
endrsubmit;
*rdisplay task1;
waitfor _all_ task1_1 task1_2 task1_3 task1_4;
signon task2 sascmd="!sascmd";
rsubmit task2 wait=no;
libname LIB '/batch/sas/SASTMP';
data LIB.D7;
retain _A _C1 _C2;
drop _:;
set LIB.C7_1 LIB.C7_2 LIB.C7_3 LIB.C7_4;
by B;
if _A lt A then do;
_A=A; _C1=C1; _C2=C2;
end;
if last.B;
A=_A; C1=_C1; C2=_C2;
run;
endrsubmit;
waitfor task2;
rget task1_1;
rget task1_2;
rget task1_3;
rget task1_4;
rget task2;

Cheers from Portugal.

Daniel Santos @ www.cgd.pt.

Re: PLZ: Macro Urgent Help

ChrisNZ — Thu, 09 Jul 2009 21:34:59 GMT

Mmm, interesting. I woud never have thought of splitting the sort.
This is probably the wrong place to prolong this discussion, though.
As usual when discussing performance, the answer is: it depends on the data and what you do with it, which makes sizing the right box for a data warehouse so difficult.