Re: how do i optimize this sql code

MK,

To do proper optimization it would take knowing your problem
because optimization involve the whole program not just a block o
code. However we can consider partial optimization of the code
segment shown hoping that this code would actually be needed in
an optimized program.

You optimize the SQL, in this case, by reducing the code to one
SELECT. You shorten the code by first determining the pattern
and then introducing a little macro. First consider your first
SELECT statement:

select count(irandtrt) into :cy40t
from phye
where trim(left(irandtrt))="1" and ecog="0" and visit=6;

It could be written
select sum(left(irandtrt)="1" and ecog="0") into :cy40t
from phye
where visit=6 ;

Now you should see that all the counts can be stuffed into one
SELECT using that form of counting.

For the pattern, I see CY4 as the constant part of the macro
names. Then a number 0 to 4 corresponding to value of ECOG and
letter (T,D,H) corresponding to the value of IRANDTRT (1,2,3).
This suggests two %DO-loops. Before writing the loop, let's
transform the single SELECT above. Say I is in (1,2,3) and J in
(0,1,2,3,4), then

select sum(left(irandtrt)="&i" and ecog="&j")
into :cy4&j%substr(TDH,&i,1)
from phye
where visit=6 ;

Thus we are led to the macro:

%macro q ( dummy ) ;
%**I am to dumb about the problem to know what parms to
use. For clarity, first generate the two required coma
separated lists, then the SQL code.
;
%local sellist maclist v ;
%do i = 1 %to 3 ;
%let v = %scan ( TDH , &i ) ;
%if &i >= 2 %then
%do ; /* add leading separator for next block */
%let sellist = &sellist , ;
%let maclist = &maclist , ;
%end ;
/* always add term after the coma */
%let sellist = &sellist
sum(left(irandtrt)="&i" and ecog="0") ;
%let maclist = &maclist :cy40%substr(TDH,&i) ;
%do j = 1 %to 4 ;
%let sellist = &sellist
, sum(left(irandtrt)="&i" and ecog="&j") ;
%let maclist = &maclist, :cy4&j%substr(TDH,&i) ;
%end ;
%end ;
/* now the select statement */
select &sellist into &maclist
from phye
where visit=6
;
%mend q ;

Is this better than

select
sum(left(irandtrt)="1" and ecog="0")
, sum(left(irandtrt)="1" and ecog="1")
, sum(left(irandtrt)="1" and ecog="2")
, sum(left(irandtrt)="1" and ecog="3")
, sum(left(irandtrt)="1" and ecog="4")
....
into
:cy40t
, :cy41t
, :cy42t
, :cy43t
, :cy44t
....
from phye
where visit=6
;

You have to be the judge. There are two important
considerations:
1) The code should help the reader see the pattern
2) The code should be written so that something modified
is modified in one place.

Usually these considerations lead one to say that the macro
code is better, at least when the reader can read macro code.
However, in this case the macro code is complex enough to raise
the question whether macro is really appropriate here.

So why does the code have to be SQL? Perhaps separating the
count process from the process of making the variables would be
better.

proc freq data = phye ;
where visit = 6 ;
table irandtrt * ecog / out = f noprint ;
run ;

proc format ;
value $temp
"1" = "t"
"2" = "d"
"3" = "h"
;
run ;

data _null_ ;
set f ;
call symput ( "CY4" || ecog || put(irandtrt,$temp1.)
, put( count, 8. -l ) ) ;
run ;

This code is clearer and shows the pattern better, since it ties
the variable values to the macro variable names in a simple line.
Moreover it would PROC FREQ probably runs faster on a large data
set.

I have assumed all combinations of variables are realized in the
data and there are no extraneous values. The first condition can
be eliminated by appropriate initialization. The second could be
eliminated by adding the condition

irandtrt in ( "1", "2", "3" )
and ecog in ( "0", "1", "2", "3", "4" )

to WHERE conditions in appropriate places.

Ian Whitlock
=================
Date: Wed, 15 Jun 2005 08:37:31 -0700
Reply-To: m k <odshtml@YAHOO.COM>
Sender: "SAS(r) Discussion"
From: m k <odshtml@YAHOO.COM>
Subject: how do i optimize this sql code
Content-Type: text/plain; charset=iso-8859-1
Hi All
Nothing is wrong with this code 'syntaxically' but I feel it to
be too long aka bad programming. Can someone shorten it down.
Thanks so much as always
MK

proc sql;
select count(irandtrt) into :cy40t from phye where
trim(left(irandtrt))="1" and ecog="0" and visit=6;
select count(irandtrt) into :cy41t from phye where
trim(left(irandtrt))="1" and ecog="1" and visit=6;
select count(irandtrt) into :cy42t from phye where
trim(left(irandtrt))="1" and ecog="2" and visit=6;
select count(irandtrt) into :cy43t from phye where
trim(left(irandtrt))="1" and ecog="3" and visit=6;
select count(irandtrt) into :cy44t from phye where
trim(left(irandtrt))="1" and ecog="4" and visit=6;
select count(irandtrt) into :cy40d from phye where
trim(left(irandtrt))="2" and ecog="0" and visit=6;
select count(irandtrt) into :cy41d from phye where
trim(left(irandtrt))="2" and ecog="1" and visit=6;
select count(irandtrt) into :cy42d from phye where
trim(left(irandtrt))="2" and ecog="2" and visit=6;
select count(irandtrt) into :cy43d from phye where
trim(left(irandtrt))="2" and ecog="3" and visit=6;
select count(irandtrt) into :cy44d from phye where
trim(left(irandtrt))="2" and ecog="4" and visit=6;
select count(irandtrt) into :cy40h from phye where
trim(left(irandtrt))="3" and ecog="0" and visit=6;
select count(irandtrt) into :cy41h from phye where
trim(left(irandtrt))="3" and ecog="1" and visit=6;
select count(irandtrt) into :cy42h from phye where
trim(left(irandtrt))="3" and ecog="2" and visit=6;
select count(irandtrt) into :cy43h from phye where
trim(left(irandtrt))="3" and ecog="3" and visit=6;
select count(irandtrt) into :cy44h from phye where
trim(left(irandtrt))="3" and ecog="4" and visit=6;
quit;