topic Re: Teradata and SAS Datasets in SAS Programming

Teradata and SAS Datasets

SASPhile — Fri, 13 Apr 2012 13:43:16 GMT

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;

Re: Teradata and SAS Datasets

Tom — Fri, 13 Apr 2012 13:56:56 GMT

Why do you have these extra characters?

...

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

disconnect from ODBC);

Re: Teradata and SAS Datasets

SASPhile — Fri, 13 Apr 2012 14:47:34 GMT

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;

Re: Teradata and SAS Datasets

Tom — Fri, 13 Apr 2012 15:09:37 GMT

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.

Re: Teradata and SAS Datasets

Patrick — Sat, 14 Apr 2012 00:40:51 GMT

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#p0gu3f7qh8i83jn18af4i1bf2xlp.htm

...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#n08fst8xffsxx6n191ais907yw9e.htm

Re: Teradata and SAS Datasets

SASPhile — Sat, 14 Apr 2012 13:31:26 GMT

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

Re: Teradata and SAS Datasets

Patrick — Sun, 15 Apr 2012 00:05:40 GMT

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.

Re: Teradata and SAS Datasets

deepakkailay — Wed, 12 Sep 2018 14:13:28 GMT

Hi,

I am using libname to create temp table and using it with proc sql to access it.

I am able to run the code on SAS EG but it is hitting the error on UNIX that Teradata prepare: Object 'migrated_201401' does not exist.

%let tag_value=migrated_201401;
options sastrace=',,,d' sastraceloc=saslog;

libname mytd teradata user=&user. password=&password. tdpid=edwprod connection=global dbmstemp=yes mode=teradata dbcommit=0;

proc delete data=mytd.&tag_value.; run;

proc append data = only_cl_tids_migrated base = mytd.&tag_value. (tpt=no dbcommit=5000) force; run;

proc sql;
reset INOBS = max OUTOBS = max LOOPS = MAX NOFLOW NOFEEDBACK NOPROMPT NONUMBER;
connect to teradata(tdpid = edwprod database = consumer user =&user. password=&password. connection=global);
create table test as
select * from connection to teradata
(
select a.*,
b.*

from &tag_value. a
join tag_tbl b /* table present on my teradata system*/
on a.acct= b.acct
);
quit;

ERROR: Teradata prepare: Object 'migrated_201401' does not exist. SQL statement was:

Please provide some guidance.

thanks in advance.

Re: Teradata and SAS Datasets

LinusH — Wed, 12 Sep 2018 14:52:51 GMT

@deepakkailay this thread is 4 years old.

If you have a new inquiry:

Search support.sas.com and this site so that your question have not already been answered
If no hit, create a new subject/thread