|
Hi, Paul,
Do I have too much confidence in the optimizer? Well, I am assuming that
put(status, $status.) returns missing(i.e. " ") when status is missing.
Cheers,
Chang
<sasl:code>
proc sql _method;
create view DropEm as
select uci
from cmf36.sas
where status not in ('0' '1' '2' '3' '8')
;
create table stat as
select distinct
coalesce(cmf00.uci, cmf06.uci, cmf12.uci, cmf18.uci,
cmf24.uci, cmf30.uci, cmf36.uci) as uci
, coalesce(put(cmf00.status, $status.), "X") as st00
, coalesce(put(cmf06.status, $status.), "X") as st06
, coalesce(put(cmf12.status, $status.), "X") as st12
, coalesce(put(cmf18.status, $status.), "X") as st18
, coalesce(put(cmf24.status, $status.), "X") as st24
, coalesce(put(cmf30.status, $status.), "X") as st30
, coalesce(put(cmf36.status, $status.), "X") as st36
from (select cmf00.uci, status from cmf00.sas as cmf00, dropEm
where cmf00.uci ^= dropEm.uci)
, (select cmf06.uci, status from cmf06.sas as cmf06, dropEm
where cmf06.uci ^= dropEm.uci)
, (select cmf12.uci, status from cmf12.sas as cmf12, dropEm
where cmf12.uci ^= dropEm.uci)
, (select cmf18.uci, status from cmf18.sas as cmf18, dropEm
where cmf18.uci ^= dropEm.uci)
, (select cmf24.uci, status from cmf24.sas as cmf24, dropEm
where cmf24.uci ^= dropEm.uci)
, (select cmf30.uci, status from cmf30.sas as cmf30, dropEm
where cmf30.uci ^= dropEm.uci)
, (select cmf36.uci, status from cmf36.sas as cmf36, dropEm
where cmf36.uci ^= dropEm.uci)
;
quit;
</sasl:code>
On Wed, 22 Oct 2003 14:30:41 -0700, Choate, Paul@DDS <pchoate@DDS.CA.GOV>
wrote:
>Dear SAS-L SQL enthusiasts -
>
>I'd like to convert the following data step code to SQL, but it includes
>conditionally setting variable values based on the merge statement 'in='
>option. It also translates the statnn variables to the st4 variables.
What
>would be the simplest or most efficient equivalent SQL code? Pre or post
>processing with a datastep would be okay, I'm just looking for a good SQL
>alternative as an example for a presentation. Would there be a processing
>advantage to using SQL? The files are sorted on the unique UCI key and
each
>contains about 500k-600k records. About 300k will meet the "IF NOT IN36
OR
>STAT36 IN ('0' '1' '2' '3' '8')" condition.
>
>
>DATA STAT (KEEP=UCI ST00 ST06 ST12 ST18 ST24 ST30 ST36);
> MERGE CMF00.SAS (KEEP=UCI STATUS RENAME=(STATUS=STAT00) IN=IN00)
> CMF06.SAS (KEEP=UCI STATUS RENAME=(STATUS=STAT06) IN=IN06)
> CMF12.SAS (KEEP=UCI STATUS RENAME=(STATUS=STAT12) IN=IN12)
> CMF18.SAS (KEEP=UCI STATUS RENAME=(STATUS=STAT18) IN=IN18)
> CMF24.SAS (KEEP=UCI STATUS RENAME=(STATUS=STAT24) IN=IN24)
> CMF30.SAS (KEEP=UCI STATUS RENAME=(STATUS=STAT30) IN=IN30)
> CMF36.SAS (KEEP=UCI STATUS RENAME=(STATUS=STAT36) IN=IN36);
> BY UCI;
>
> IF NOT IN36 OR STAT36 IN ('0' '1' '2' '3' '8');
>
> IF NOT IN00 THEN STAT00='X';
> IF NOT IN06 THEN STAT06='X';
> IF NOT IN12 THEN STAT12='X';
> IF NOT IN18 THEN STAT18='X';
> IF NOT IN24 THEN STAT24='X';
> IF NOT IN30 THEN STAT30='X';
> IF NOT IN36 THEN STAT36='X';
>
> ST00=PUT(STAT00,$STATUS.);
> ST06=PUT(STAT06,$STATUS.);
> ST12=PUT(STAT12,$STATUS.);
> ST18=PUT(STAT18,$STATUS.);
> ST24=PUT(STAT24,$STATUS.);
> ST30=PUT(STAT30,$STATUS.);
> ST36=PUT(STAT36,$STATUS.);
>Run;
>
>Thanks for your time -
>
>Paul Choate
>DDS Data Extraction
>(916) 654-2160
|
