Need a macro to run daily data for unlimited number of days

[kajal_30]

I have a code which I am running to join data for certain number of days but now I need to extend the same code for unlimited number of days using a macro. Can anyone please help.

Regards

Kajal 

proc sql;
create table f.rprt as 
select 
a.TY_NUMBER as TY_NUMBER,
a.IO_NUMBER  as  a_IO_NUMBER,		
b.IO_NUMBER  as  b_IO_NUMBER,		
c.IO_NUMBER  as  c_IO_NUMBER,		
d.IO_NUMBER  as  d_IO_NUMBER,
e.IO_NUMBER  as  e_IO_NUMBER,

a.GN_ID  as  a_GN_ID,		
b.GN_ID  as  b_GN_ID,		
c.GN_ID  as  c_GN_ID,		
d.GN_ID  as  d_GN_ID,
e.GN_ID  as  e_GN_ID,
a.ON_ID  as  a_ON_ID,		
b.ON_ID  as  b_ON_ID,		
c.ON_ID  as  c_ON_ID,		
d.ON_ID  as  d_ON_ID,
e.ON_ID  as  e_ON_ID,

a.C_TYPE  as  a_C_TYPE,		
b.C_TYPE  as  b_C_TYPE,		
c.C_TYPE  as  c_C_TYPE,		
d.C_TYPE  as  d_C_TYPE,
e.C_TYPE  as  e_C_TYPE,

a.ME_ID  as  a_ME_ID,		
b.ME_ID  as  b_ME_ID,		
c.ME_ID  as  c_ME_ID,		
d.ME_ID  as  d_ME_ID,
e.ME_ID  as  e_ME_ID 
from out.heads_20 as a 
left join 
out2.heads_21 as b
on a.TY_NUMBER = b.TY_NUMBER and a.VE_DATE = b.VE_DATE
left join 
out3.heads_23 as c
on b.TY_NUMBER = c.TY_NUMBER and b.VE_DATE = c.VE_DATE
left join 
out4.heads_26 as d 
on c.TY_NUMBER = d.TY_NUMBER and c.VE_DATE = d.VE_DATE
left join 
out5.heads_28 as e 
on d.TY_NUMBER = e.TY_NUMBER and d.VE_DATE = e.VE_DATE
;
quit;

[PaigeMiller]

---

@kajal_30 wrote:

I have a code which I am running to join data for certain number of days but now I need to extend the same code for unlimited number of days using a macro. 

---

Is your question: how to run this code every day?

Or is the question how to modify the code so it runs properly on a future day?

Or is the question something else?

[kajal_30]

Initially I was running this code for few days let say 5 so I was able to hardcode all days dates in the join condition but now I need to run this for about 33 days so need a macro which can automatically replace the table name in the join condition to use all available dates and join data for all those 33 days.

Regards

Kajal 

[PaigeMiller]

Thanks, I think I understand now.

Suggestion: instead of this data structure

a.IO_NUMBER  as  a_IO_NUMBER,		
b.IO_NUMBER  as  b_IO_NUMBER,		
c.IO_NUMBER  as  c_IO_NUMBER,		
d.IO_NUMBER  as  d_IO_NUMBER,
e.IO_NUMBER  as  e_IO_NUMBER,

which creates a wide data set, much easier to program if you create a long data set instead. And also it is much easier to perform follow-up analyses when you have a long data set.

If you'd like to go that way, let me know.

[kajal_30]

yeah that seems better idea plz put the snapshot code here I will use that

Regards

Kajal

[PaigeMiller]

data f.rprt;
    set out.heads_20 out2.heads_21 out3.heads_23 out4.heads_26 out5.heads_28
        indsname=indsname;
    by ty_number ve_date;
    orig_dsn=indsname;
run;