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;
@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?
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
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.
yeah that seems better idea plz put the snapshot code here I will use that
Regards
Kajal
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: