11-01-2012 03:25 PM
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:
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 ;
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
11-01-2012 03:45 PM
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).
11-01-2012 03:54 PM
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..
11-01-2012 04:03 PM
I don't have teradata, thus I can't test an example.
Both of the following work for me:
create table want (drop=sex) as
create table want as
from sashelp.class (drop=sex)
11-01-2012 06:34 PM
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.