Help using Base SAS procedures

Exclude columns using proc SQL

Reply
Super Contributor
Posts: 401

Exclude columns using proc SQL

Hi.. I know there are many SQl experts here (obviously I'm not one). .. I'm trying to link 2 tables, one is a datawarehous eon 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 s

elect 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

PROC Star
Posts: 7,492

Re: Exclude columns using proc SQL

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

Super Contributor
Posts: 401

Re: Exclude columns using proc SQL

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

PROC Star
Posts: 7,492

Re: Exclude columns using proc SQL

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;

Super Contributor
Posts: 349

Re: Exclude columns using proc SQL

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

Super User
Super User
Posts: 7,078

Re: Exclude columns using proc SQL

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 only list 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.

Ask a Question
Discussion stats
  • 5 replies
  • 8003 views
  • 0 likes
  • 4 in conversation