BookmarkSubscribeRSS Feed
SASPhile
Quartz | Level 8

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;

8 REPLIES 8
Tom
Super User Tom
Super User

Why do you have these extra characters?

...

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

disconnect from ODBC);

SASPhile
Quartz | Level 8

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;

Tom
Super User Tom
Super User

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.

Patrick
Opal | Level 21

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

SASPhile
Quartz | Level 8

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

Patrick
Opal | Level 21

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.

deepakkailay
Calcite | Level 5

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.

LinusH
Tourmaline | Level 20

@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
Data never sleeps

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 5610 views
  • 0 likes
  • 5 in conversation