Left Join to multiple separatable tables in one sql step

[TheNovice]

Hi,

I have a dataset Q with Jan and Feb data (With duplicates). I have 2 month end datasets for Jan and Feb. I need to join the Jan data in Q to Jan ME and Feb data in Q to Feb ME. Variables in both ME datasets are named the same

Doing it separately in 2 steps is obviously easy but is it possible to do it in one proc sql step ? This would be helpful as the dataset Q gets populated with more months.

The ME sets are in format XY_20190131 and XY_20190228

Any direction would be appreciated.

[PaigeMiller]

Something like this:

proc sql;
    create table abc as select a.variable1,a.variable2,b.variable3,c.variable4
        from a left join b on a.variable1=b.variable1
        left join c on a.variable1=c.variable1;
quit;

[TheNovice]

Thank you Paige,

The output I need would need to look like the End result table.

The orig table is the main table and there is a jan result and feb result table. I want to join to the respective month end tables based on the month listed in the original table on a.id = b.id or whatnot. 

But can joining to 2 tables in step be done in one proc sql statement?

[PaigeMiller]

---

@TheNovice wrote:

But can joining to 2 tables in step be done in one proc sql statement?

---

I believe I already showed it could be done, and provided an example of code.

[s_lassen · Accepted solution]

It is not a good idea to have separate datasets for each month. But sometimes, that's what you get.

The easy way to handle this case may be to collect the monthly data in a data step view before joining, e.g.:

data XY_2019/View=XY_2019;
  set inlib.XY_2019: indsname=dsname;
  filedate=input(scan(dsname,2,'_'),yymmdd8.);
  format filedate date9.;
run;

You can then join your Q table to that using a standard SQL left join.

[TheNovice]

Thank you very much!!