performance problems???

sassharp — Wed, 06 Mar 2013 16:49:16 GMT

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

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?

Re: performance problems???

UrvishShah — Thu, 07 Mar 2013 13:07:36 GMT

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

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