topic Re: Proc Sql and Drop in SAS Programming

Proc Sql and Drop

Xinxin — Fri, 09 Aug 2019 20:36:27 GMT

Hello,

I am joining 2 tables and this is my syntax:

proc sql;
create table final1 as select a.*, b.* from main1 a left join main2 b
on a.ACCOUNT_ID = b.ACCOUNT_ID
; quit;

And I know why I get the following note: WARNING: Variable ACCOUNT_ID already exists on file WORK.FINAL1

Both a and b data sets have many columns and I don't want to list out ALL the columns of b and just eliminate ACCOUNT_ID.

I tried using DROP at various places but did not work.

What is the method with proc sql if you want to keep a lot more columns than you want to drop, when joining tables. (In this ex I want to drop only 1 but my query is in general)

Thanks!

Re: Proc Sql and Drop

hashman — Fri, 09 Aug 2019 21:31:31 GMT

@Xinxin:

It can be done by using a little rename/drop subterfuge:

proc sql ;                                                    
  create table final1 (drop=account_id2) as                   
  select *                                                    
  from   main1 left join main2 (rename=account_id=account_id2)
  on     account_id = account_id2                             
  ;                                                           
quit ;

Another advantage of this trick is that you don't even have to qualify the tables being joined - provided, of course, that table MAIN2 has no column named ACCOUNT_ID2. To be even more on the safe side, you can use something wild like ___AID instead of ACCOUNT_ID2 (use as many leading underscores as need be to ensure the column doesn't exist in the tables being joined).

AFAIK, with FedSQL in Viya, you can use the USING clause to achieve the effect without jumping through hoops like above:

proc sql ;                                                    
  create table final1 (drop=account_id2) as                   
  select *                                                    
  from   main1 left join main2
  using (account_id)                             
  ;                                                           
quit ;

Documented at:

https://documentation.sas.com/?docsetId=casfedsql&docsetTarget=p1nh3lts36c17yn1og1qwpoiznvb.htm&docsetVersion=3.4&locale=en

In my 9.4 it doesn't work, though, so I use the rename/drop recipe to get what I want.

Kind regards

Paul D.

Re: Proc Sql and Drop

PGStats — Fri, 09 Aug 2019 22:49:18 GMT

Since account_id is the only field in common, you can also do:

proc sql ;                                                    
  create table final1 as                   
  select *                                                    
  from main1 natural left join main2;             
  ;                                                           
quit ;

Re: Proc Sql and Drop

hashman — Fri, 09 Aug 2019 23:10:31 GMT

@PGStats:

Egad! You've just dispelled my confusion that "natural" is applicable to inner joins only. Not that I haven't tried, but in my utter stupidity, I coded "left natural" rather than "natural left", having neglected to try it in the opposite order ... duh.

That said, compared to the rename/drop or using the USING clause, a natural join creates a program-control limitation by auto-joining on all the columns with the same names and data types. Methinks it would be best if the USING clause in this sense were made applicable to all SAS SQL flavors. But I'm afraid there exists a serious obstacle, since though in Base the clause does exist, it is already used for a different purpose (related to working with external data bases).

Kind regards

Paul D.

Re: Proc Sql and Drop

Reeza — Sat, 10 Aug 2019 02:31:46 GMT

One other option, but not as succinct as the others one here.

Use the FEEDBACK option on the PROC SQL and check the log. It will have the fully generated code.

data bmi;
set sashelp.class;

bmi = weight*703 / (height**2) ;

keep name bmi;
run;


proc sql FEEDBACK;
create table want as
select *
from sashelp.class as a
left join bmi as b
on a.name=b.name;
quit;

The FEEDBACK option puts this into the log:

 78         proc sql FEEDBACK;
 79         create table want as
 80         select *
 81         from sashelp.class as a
 82         left join bmi as b
 83         on a.name=b.name;
 NOTE: Statement transforms to:
 
         select A.Name, A.Sex, A.Age, A.Height, A.Weight, B.Name, B.bmi
           from SASHELP.CLASS A left outer join WORK.BMI B on A.Name = B.Name;
 
 WARNING: Variable Name already exists on file WORK.WANT.
 NOTE: Table WORK.WANT created, with 19 rows and 6 columns.

You can easily filter the columns now.

Re: Proc Sql and Drop

Xinxin — Mon, 12 Aug 2019 16:56:46 GMT

Wow....genius! I can do this with multiple tables too! Thanks a lot!!

Re: Proc Sql and Drop

Xinxin — Mon, 12 Aug 2019 16:58:55 GMT

Oh yes, thank you Reeza...I had forgotten about this option!

Re: Proc Sql and Drop

Xinxin — Mon, 12 Aug 2019 17:00:39 GMT

Thank you very much....this worked!