topic Re: how will you optimize below code? in New SAS User

how will you optimize below code?

Sukhi1 — Mon, 15 Oct 2018 14:46:16 GMT

proc sql;
create table work.EXUPL_GVA2_&YYMM. as

        select R.*,
               S.FIL as FILMAP, S.KTO as KTOMAP
         from work.EXUPL_GVA1_&YYMM. as R

          left join LIB_CLNT.CLIENT2SEGMENT_FKN_FINAL_&YYMM.
               (where =( FIL and KTO <=0)) as S
           on R.ORG_ID=S.ORG_ID
          ;
quit;

data work.EXUPL_GVA3_&YYMM.;
set work.EXUPL_GVA2_&YYMM.;

if KTO eq 9999989 and KTOMAP not in (. 9999989) then do;

FIL=FILMAP;
KTO=KTOMAP;

end;
run;

proc sql;
       create table work.EXUPL_GVA4_&YYMM. as
             select R.*
                   ,S.PCC
                   ,S.GBM
             from work.EXUPL_GVA2_&YYMM. as R
             left join LIB_CLNT.CLIENT_MASTER_&YYMM. as S
             on    R.FIL=S.FIL and R.KTO=S.KTO
             ;
quit;

Re: how will you optimize below code?

sidpesar — Mon, 15 Oct 2018 16:56:57 GMT

on work.EXUPL_GVA4_&YYMM from table is EXUPL_GVA2_&YYMM. or EXUPL_GVA3_&YYMM

the below code will work if you want to use EXUPL_GVA3_&YYMM to create work.EXUPL_GVA4_&YYMM.

PROC SQL;
CREATE TABLE WORK.EXUPL_GVA4_&YYMM. AS

SELECT
S.FIL AS FILMAP, 
S.KTO AS KTOMAP,
(CASE WHEN S.KTO EQ 9999989 AND S.KTOMAP NOT IN (. 9999989) THEN S.FIL) END) AS FIL,
(CASE WHEN S.KTO EQ 9999989 AND S.KTOMAP NOT IN (. 9999989) THEN S.KTO) END) AS KTO, 
S.PCC,
S.GBM,
R.*

FROM  
WORK.EXUPL_GVA1_&YYMM. AS R
LEFT JOIN LIB_CLNT.CLIENT2SEGMENT_FKN_FINAL_&YYMM.(WHERE =( FIL AND KTO <=0)) AS S
ON  R.ORG_ID=S.ORG_ID
LEFT JOIN LIB_CLNT.CLIENT_MASTER_&YYMM. AS S1
ON    S.FIL=S1.FIL AND S.KTO=S1.KTO;
QUIT;

Re: how will you optimize below code?

ballardw — Mon, 15 Oct 2018 20:12:25 GMT

Optimize can mean several things: Create code that runs in minimum execution time; uses minimum disk or memory; minimizes code lines; runs robustly in spite of "bad" data and I'm sure a few more could be considered.

Which do you mean to optimize?

Re: how will you optimize below code?

Sukhi1 — Tue, 16 Oct 2018 05:02:17 GMT

Hi ,
For now minimum lines of code and less execution time would be good for me.
Thanks a lot