topic Re: Left Join to multiple separatable tables in one sql step in SAS Programming

Left Join to multiple separatable tables in one sql step

TheNovice — Mon, 11 Mar 2019 17:05:06 GMT

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.

Re: Left Join to multiple separatable tables in one sql step

PaigeMiller — Mon, 11 Mar 2019 17:09:04 GMT

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;

Re: Left Join to multiple separatable tables in one sql step

TheNovice — Mon, 11 Mar 2019 17:32:08 GMT

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?

Re: Left Join to multiple separatable tables in one sql step

PaigeMiller — Tue, 12 Mar 2019 00:26:17 GMT

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

Re: Left Join to multiple separatable tables in one sql step

s_lassen — Tue, 12 Mar 2019 10:58:07 GMT

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.

Re: Left Join to multiple separatable tables in one sql step

TheNovice — Wed, 13 Mar 2019 02:36:55 GMT

Thank you very much!!