performance problems???

[sassharp]

data x(index =(id));

set y;

where (code1 in ('1','2','3','4','5','6','7','8')) and code2 in ('12');

keep c1id c2  ..... c2000;/* ... is hard coded columns c3 to c1999*/

run;

proc sql;

create table w1 as

select  x.c1id,c2,.....,c2000,t1.a1,.....t1.a500

from x

left join

t1

on x.id=t1.id;

index on x.id ;

run;

like above created tables w1 to w400 by changing left join tables t1 to t400

now merged all the tables w1 through w400 all by id.

data final;

merge w1 to w400;

by id;

run;

Q) tested the code fine and working for one id. But when running this code for 2 million id's it is taking longer time.

any suggestions to work this code faster?

[UrvishShah]

Hi,

based on your existing SAS Code, you can join all the datasets in one Proc Sql step rather than merging the datasets in data step...

Proc sql;

      create table final as

      select 'variable you want'

      from x                                 left join

             t1

                                                on

             x.id=t1.id                      left join

             t2                                on

            x.id = t2.id                     left join

            t3                                 on

           x.id = t3.id                     left join

           t4                                  on

           x.id = t4.id                   

.

.

.

.

follow this process in %DO Iterative loop in Macro.....so that you dont need to write join conditions...

Just let me know if you are getting any error in using this logic in %DO Loop in Macro...

Thanks,

Urvish