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?
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.
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.)
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.
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 25. 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.