Macro - left join multiple views

[MargoBlue]

I will be grateful for the answer if it's possible to make below left joining without overwriting values in the last column (col5).

I have proc sql:

proc sql;

create table tab3 as

select a.id1, a.date1, a.col3, a.col4, b.col5

from tab1 a

left join tab_date b /*tab_date - views such as tab_201301, tab_201302,..., tab_202001 */

on a.id1 = b.id1 and a.date1=b.date1;

quit;

I have multiple (quite big) views, from which one I want to join col5. Each view has data for one month. for example: tab_201301 - there are records where column date1 = "31jan2013"d

First I thought about concatenate all views, create new table and make left join. But this table will be very large and I am afraid that it will not work.

I think to create macro:

%macro example(date_st, period);

%local i date_new;

%let date_new = %sysfunc(inputn(&date_st,yymmn6.));

%do i=0 %to %sysevalf(&period);

proc sql;

create table tab3 as

select a.id1, a.date1, a.col3, a.col4, b.col5

from tab1 a

left join

tab_%sysfunc(intnx(month,&date_new,&i),yymmn6.);

b;

on a.id1 = b.id1 and a.date1=b.date1;

quit;

%end;

%mend;

But it overwrites values in col5.

Is it possible to join views each one without concatenating them first?

[PaigeMiller]

Since you are trying to do this in a loop, and the data set names follow some sort of regular pattern tab_%sysfunc(intnx(month,&date_new,&i),yymmn6.), you need to do the joins in your loop and rename col5 to col5_%sysfunc(intnx(month,&date_new,&i),yymmn6.), something like this:

select a.id1, a.date1, a.col3, a.col4, 
     b.col5 as col5_%sysfunc(intnx(month,&date_new,&i),yymmn6.)

[Reeza]

Stack all the data and then transpose them. Use a view for the stacking as well.

[PaigeMiller · Accepted solution]

Or better yet, stack all the views and then leave them in the long (rather than wide) form, and then you can do analyses using the a BY statement rather than having to code an analysis for col5_201301 and another for col5_201302 etc.

[MargoBlue]

Thank you, it works.