topic Re: Join two tables of different legth without using specific joins in SAS Programming

Join two tables of different legth without using specific joins

mhoward2 — Wed, 26 Jun 2019 13:48:22 GMT

Ill start off by saying what I need to complete must only be done using the Proc Sql Procedures in Enterprise Guide since I only have VBA set execute Proc Sql statements when connected to the SAS server.

Is it possible to take a table with say 5 columns and 50 rows and add another table and another table with 2 columns and 55 rows side by side without anything in common to join on? Just start at row one for each and go down from there? Thanks!

Re: Join two tables of different legth without using specific joins

PeterClemmensen — Wed, 26 Jun 2019 13:51:07 GMT

Welcome to the SAS Communities 🙂

Sounds like a Merge Statement without a By Statement can do the trick for you

data want;
   merge sashelp.iris sashelp.class;
run;

Re: Join two tables of different legth without using specific joins

mhoward2 — Wed, 26 Jun 2019 13:54:28 GMT

Hi! Thanks for taking the time to help! As mentioned though I'm only looking for solutions that use PROC SQL and not data steps since I cant use them when connected to the SAS server through VBA. Is this possible with PROC SQL? Thanks!

Re: Join two tables of different legth without using specific joins

Tom — Wed, 26 Jun 2019 15:15:16 GMT

Why wound connecting via VBA limit you to using only one PROC?

Did some one write you a specific module that wraps all of your SAS code with PROC SQL and QUIT statements?

Re: Join two tables of different legth without using specific joins

mhoward2 — Wed, 26 Jun 2019 16:56:58 GMT

I was given the VBA Code that connects to the SAS Server and was told it only supports PROC SQL. I know it can handle more if coded to but I don't know how to write that code. Do you have experience in that or can point me somewhere to learn more? I've tried googling a ton to no avail. Thanks!

Re: Join two tables of different legth without using specific joins

PGStats — Wed, 26 Jun 2019 19:04:14 GMT

Short answer: No. You can't join two tables one-to-one in SAS/SQL without a join condition. Joining without a join condition is allowed, but results in the cartesian product of the tables.

Re: Join two tables of different legth without using specific joins

Tom — Wed, 26 Jun 2019 19:11:58 GMT

Long answer, perhaps, if you trust the undocumented MONOTONIC() function.

proc sql ;
create table want as 
select * 
from (select name,monotonic() as arow from sashelp.class(obs=10)) a
full join 
     (select name as name2,age,monotonic() as brow from sashelp.class(obs=5)) b
on a.arow = b.brow
;
quit;

Obs    Name       arow     name2     Age    brow

  1    Alfred       1     Alfred      14      1
  2    Alice        2     Alice       13      2
  3    Barbara      3     Barbara     13      3
  4    Carol        4     Carol       14      4
  5    Henry        5     Henry       14      5
  6    James        6                  .      .
  7    Jane         7                  .      .
  8    Janet        8                  .      .
  9    Jeffrey      9                  .      .
 10    John        10                  .      .