DATA Step, Macro, Functions and more

Teradata and SAS Datasets

Reply
Super Contributor
Posts: 647

Teradata and SAS Datasets

I would want to create SAS dataset _tmp in my work library.I'm not able to do using the following syntax.

PROC SQL;

connect to teradata as ODBC
(user='ac43927' password='summer01' tdpid=dwprod1  database='ac43927' CONNECTION = GLOBAL );
create table _tmp as
select *
from connection to teradata
(
SELECT DISTINCT   a.unique_id
FROM   &exp_tbl.    a
WHERE  ORIGIN IN ('INCOM','KYCOM','OHCOM','MOCOM','WICOM')  /* Commercial   */
/* ('INPPO','KYPPO','OHPPO','MOPPO','WIPPO' )   */
And sex = 'F'                             /* Females  */
And  AGE_DEC31&measure_yr. between 16 and 24          /* Age 16-24 */ 
And    CONTENR&measure_yr. = 1            /* one year continuos enrollment  */
And        GAP&measure_yr. <=1            /* no more than one gap  */
And     ANCHOR&measure_yr. = 1         /* members enrolled at end of year  */
And   exposure&measure_yr. > 0      /* and have medical benefits   */
  
WITH DATA
PRIMARY INDEX( unique_id )
ON COMMIT PRESERVE ROWS  ) by ODBC;
execute (commit) by ODBC;

disconnect from ODBC);
quit;

Super User
Super User
Posts: 6,499

Re: Teradata and SAS Datasets

Why do you have these extra characters?

...

ON COMMIT PRESERVE ROWS  ) by ODBC;
execute (commit) by ODBC;

disconnect from ODBC);

Super Contributor
Posts: 647

Re: Teradata and SAS Datasets

Thanks works fine now,

One more question.How do I reference SAS datasets in proc sql?For instance in the code below _female is  created in SAS work.But in the following I get an error

 

ERROR: Teradata prepare: Object '_female' does not exist.

PROC SQL;

connect to teradata as ODBC
(user='ac43927' password='summer01' tdpid=dwprod1 CONNECTION = GLOBAL );
create table _tmp as
select *
from connection to teradata
(
  SELECT DISTINCT
      cnslkey1 as   consol_mbr_key , b.unique_id 
   FROM   &exp_tbl.     a
   inner join  _female b
    on a.unique_id = b.unique_id
);
disconnect from ODBC;
quit;

Super User
Super User
Posts: 6,499

Re: Teradata and SAS Datasets

You don't.  You need to somehow move the values of UNIQUE_ID from the SAS side to the TERADATA side so that they can be used by the database.  In general you probably want to keep as much processing in the Teradata database as possible. I assume your tables are large, why else invest in a Teradata database.

1) If the number of values of UNIQUE_ID is small then you can pull them into a macro variable and pass that back to Teradata as part of the query.  If your ID variable is numeric then it would look something like this.  Macro variables have a 32K character limit.  So if you ids are 7 digits then you can only pass about 4K ids with this method.

select distinct unqiue_id in :idlist seperated by ',' from _female;

select * from teradata (select ....  where unique_id in (&idlist) ...);

2) If the source of the table _FEMALE is from data in Teradata then restructure your query run totally in Teradata using a join or other SQL code.

3) Look into using the TERADATA libref engine and see if SAS is smart enough to push the query into the database.

Respected Advisor
Posts: 3,889

Re: Teradata and SAS Datasets

Adding to Tom's 3rd point:

You could first load your SAS table into a global temporary table in Teradata and then do everything else in Teradata: http://support.sas.com/documentation/cdl/en/acreldb/63144/HTML/default/viewer.htm#p0gu3f7qh8i83jn18a...

...or you could use the TERADATA libref engine and then investigat what option "MULTI_DATASRC_OPT" can do for you: http://support.sas.com/documentation/cdl/en/acreldb/63144/HTML/default/viewer.htm#n08fst8xffsxx6n191...

Super Contributor
Posts: 647

Re: Teradata and SAS Datasets

I'm getting this error ERROR: Teradata execute: Object 'W34test' does not exist.

I created a dataset load in SAS work library and then used append procedure to append the dataset to teradata using the follwing

LIBNAME W TERADATA USER='ac43927' PASSWORD='summer01' TDPID=dWprod1 DATABASE='ac43927'  DEFER=YES CONNECTION = GLOBAL  DBMSTEMP=YES ;

PROC APPEND DATA = WORK.load     BASE = W.W34test ;

RUN;

when i run the following query:

PROC SQL;
      /*CONNECT TO TERADATA AS DBCON      (USER='AA00792' PASSWORD='psalm121' TDPID=W)*/
        CONNECT TO TERADATA AS DBCON (USER='ac43927' PASSWORD='summer01' database='ac43927' TDPID=dWprod1);
EXECUTE
( CREATE  VOLATILE TABLE ac43927.W34_HEDISMBRS as
     (
      SELECT DISTINCT
                    ld.UNIQUE_ID
                  ,EXP2.cnslkey1          (DEC(15,0))       AS CNSL_MBR_KEY
                  ,CNSL.mbr_key
                  ,EXP2.SID
                  ,EXP2.Mbr_NO
                  ,EXP2.SEX
                  ,EXP2.LASTNAME  
                  ,EXP2.LASTNAME_158
                  ,EXP2.FRSTNAME
                  ,MBR.SSN                (CHAR(9))         AS MEM_SSN
                  ,EXP2.DOB
                  ,EXP2.ZIP5
                  ,EXP2.GROUP_ID                            AS GID
                  ,EXP2.COUNTY                                    AS MEM_COUNTY
                  ,EXP2.ASAHSA     
                  ,EXP2.HSA
                  ,EXP2.HSA_DESCRIPTION
                  ,EXP2.PRODCD
                  ,EXP2.PROD_TYPE_DESC                      AS PRODUCT
                  ,EXP2.SUB_TYPE_DESC                       AS PRODUCT_SUB_TYPE
                  ,EXP2.GL_BUS_UNIT_CD
                  ,gl.GL_BUS_UNIT_CD_DESC 
                  ,EXP2.ORIGIN                                   
                  ,EXP2.TOTAL_SOURCES
                  ,EXP2.AGE_DEC312011

from HEDIS_MBR.TRISTATE_EXP_HMO  EXP2 inner join W34test ld
       on exp2.unique_id=ld.unique_id
       inner join ADW_4.CONSOL_MBR        CNSL
     on exp2.cnslkey1=CNSL.CONSOL_MBR_KEY
              AND EXP2.cnslkey1<>0 
              AND EXP2.EXPOSURE2011>0                       /* Medical Benefit for 2011 */
              AND EXP2.CONTENR2011=1
              AND EXP2.GAP2011<=1
              AND EXP2.ANCHOR2011=1      
             AND EXP2.ORIGIN IN('WICOM','MOCOM','INCOM','KYCOM','OHCOM')
              AND EXP2.AGE_DEC312011 BETWEEN 3 AND 6
              AND CNSL.CLOS_DT='20991231'

      left outer join ADW_4.gl_bus_unit_cd_dom gl
      on EXP2.GL_BUS_UNIT_CD=gl.GL_BUS_UNIT_CD
             
    left outer JOIN ADW_4.MBR          MBR  
     ON CNSL.MBR_KEY = MBR.MBR_KEY

Respected Advisor
Posts: 3,889

Re: Teradata and SAS Datasets

Suggest you use option "OPTIONS SASTRACE=',,,d' SASTRACELOC=saslog NOSTSUFFIX;" at the beginning of your code. This will show you in the log how SAS actually loads data into your data base (what SQL is used by Proc Append).

Another thing:

"... inner join W34test ld"

I would assume you also need to use a 2 level name here (<database>.<table>).

I also recommend that you don't post information like user and password into a public forum.

Ask a Question
Discussion stats
  • 6 replies
  • 3281 views
  • 0 likes
  • 3 in conversation