topic Re: Macro - left join multiple views in SAS Programming

Macro - left join multiple views

MargoBlue — Fri, 21 Feb 2020 14:43:17 GMT

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.);

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?

Re: Macro - left join multiple views

PaigeMiller — Fri, 21 Feb 2020 15:16:23 GMT

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

Re: Macro - left join multiple views

Reeza — Fri, 21 Feb 2020 15:25:47 GMT

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

Re: Macro - left join multiple views

PaigeMiller — Fri, 21 Feb 2020 15:28:52 GMT

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.

Re: Macro - left join multiple views

MargoBlue — Mon, 02 Mar 2020 14:15:36 GMT

Thank you, it works.