topic Re: Exclude columns using proc SQL in SAS Procedures

Exclude columns using proc SQL

podarum — Fri, 21 Sep 2018 12:33:40 GMT

Hi.. I know there are many SQL experts here (obviously I'm not one). .. I'm trying to link 2 tables, one is a data warehouse on the server and the other is on my local machine.. How can I select only certain columns from the the server table. Here is the code I have:

rsubmit;
PROC SQL;
 connect to server(user=&user. password=&password. Mode=TTTTT 
 tdpid=ssss);

create table ABC.AAA_BBB (compress=yes reuse=yes) as
 select distinct a.*, b.*
 from ABC.AAA_DDD a join (Select * from connection to teradata    
    (select * from SDW001.STN_DLY)) b
 on b.ast_id = a.ast_id
 order by a.ast_id ;
quit;
endrsubmit;

SDW001 has 20 columns , 4 that are shared between ABC.AAA_DDD, but I only want all but 3 columns.. I want to drop the ones that are shared between ABC.AAA_DDD and SDW001

Re: Exclude columns using proc SQL

art297 — Thu, 01 Nov 2012 19:45:38 GMT

when you specify the tables in your from statement (e.g., from ABC, etc) you can use data set options like keep and drop. e.g.:

from ABC (keep=this that something_else),BBB (drop=var6 var_whatever).

Re: Exclude columns using proc SQL

podarum — Thu, 01 Nov 2012 19:54:01 GMT

How would I specify (using my code as an example) that I don't want to include CAPTR_DT from the SDW001.STN_DLY table ?.. because I have tried wiht drop and keep in different areas of the code and nothing worked for me..

Re: Exclude columns using proc SQL

art297 — Thu, 01 Nov 2012 20:03:20 GMT

I don't have teradata, thus I can't test an example.

Both of the following work for me:

proc sql;

create table want (drop=sex) as

select *

from sashelp.class

;

quit;

proc sql;

create table want as

select *

from sashelp.class (drop=sex)

;

quit;

Re: Exclude columns using proc SQL

shivas — Thu, 01 Nov 2012 20:04:03 GMT

Hi,

Select only the list of variable you want in the output table...for eg

instead of

select * from

try using

select col1,col2,col3

Thanks,

Shiva

Re: Exclude columns using proc SQL

Tom — Fri, 21 Sep 2018 12:36:42 GMT

Your existing code probably actually does what you want. When you use SELECT A.*,B.* and there are variables in the table referenced by B that also exist in the table referenced by A then SAS will not add them to the output dataset.

But the best solution is to list only the fields you need in the pass through SQL query. This will make sure that you only move data that you need from the Teradata server to the SAS server. You can query the SAS or Teradata metadata to get a list of the variables and just delete the three that you do not want. If you are using Teradata's free SQL Assistant application it is easy to get the list by just right clicking on the table name and selecting browse.

For the DROP/KEEP approach, as @art297 said:

Both of the following work for me:

proc sql;
  create table want (drop=sex) as
    select *
      from sashelp.class
  ;
quit;


proc sql;
  create table want as
    select *
      from sashelp.class (drop=sex)
  ;

quit;