PROC SQL select all of 2 tables and specifics in another

Question

I have code like this

 PROC SQL;
 CREATE TABLE my_table as
 SELECT DISTINCT(
   t1.*,
   t2.*,
   t3.value3 )
 FROM table1 as t1
 INNER JOIN table2 as t2
     ON  t1.value = t2.value
 INNER JOIN t3.value as t3
    ON  t1.value2 = t3.value2
 ;
  quit;

But SAS sees t1.* as a format. what can I do about this? Is there a way to express this without building 2 separate tables first?

I don't think you need the parentheses. What error do you get? — DWal, Jun 05 '15 at 19:33
it is wrong "ON t2.value = t2.value" in your code. it is "ON t1.value = t2.value". — Shenglin Chen, Jun 05 '15 at 19:37
It IS the parenthesis. I changed to distinct (t1*) , Distinct(t2), etc and now it seems to work. Thank you (sorry I mean t1=t2 value I changed the table names to post. — Jacob Ian, Jun 05 '15 at 19:39
`DISTINCT` should only be present once. `SELECT DISTINCT T1.*, t2.*, t3.value3 from ...` is correct. Putting it in multiple places is not helpful. — Joe, Jun 05 '15 at 20:19
Not sure if this is the problem, but what do you mean by "INNER JOIN t3.value as t3" ? Is there a library name 't3' and a table named 'value' in 't3' library? If that is the case, 't3' is used again in your query, but this time to refer a table? — Haikuo Bian, Jun 05 '15 at 20:30

yukclam9 · Answer 1 · 2015-06-06T09:42:04.630

Distinct

needs not the parenthes and it applies to all items listed under

Select

I suggest you to consider the use of

Coalesce

or

Coalescec ( for character)

For the duplicated variables existed at both t1 and t2. Cartisan product would return some duplicated attributes that is not preferable.

An alternative would be to separate the sql statements and it would be much clear

 Proc sql;
 Select distinct * from table1 as L1
 Inner join
 Select distinct * from table 2 as L2 
 On L1.value =L2.value
 Inner join
 ...

score 0 · Answer 2 · answered Jun 08 '15 at 00:26

You may have another problem with the code.

By using t1.* and t2.* you end up with two variables named value which SAS will not condone.

I'm afraid the best syntax you can use in SAS would be:

PROC SQL;
     CREATE TABLE my_table as
     SELECT DISTINCT(
       t1.*,
       t2.value2,
       t3.value3 )
     FROM table1 as t1
     INNER JOIN table2 as t2
         ON  t1.value = t2.value
     INNER JOIN t3.value as t3
        ON  t1.value2 = t3.value2
     ;
 QUIT;

PROC SQL select all of 2 tables and specifics in another

2 Answers2