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;
Why do you have these extra characters?
...
ON COMMIT PRESERVE ROWS ) by ODBC;
execute (commit) by ODBC;
disconnect from ODBC);
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;
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.
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...
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
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.
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.
@deepakkailay this thread is 4 years old.
If you have a new inquiry:
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.