Frequently I have a table with member IDs and index dates that I want to load into Teradata as a driver table. I use this driver table to limit a claims extraction to a time period relative to the index date field values. For example, I want to pull pharmacy claims for a specific set of members who filled a prescription in the time period between 90 days prior to the index date and 30 days after the index date occurred. Sometimes I load my table into Teradata and run my claims extraction but the query results in an empty table and no error messages. Usually this is because Teradata does not recognize my SAS date (drv.index_dt) in the WHERE clause. I can return the results I want by simply reformatting my SAS date as yymmdd10. before loading the table into Teradata as follows:

%let drv = &sysuserid;
proc sql;
create table drv as
select distinct
individual_id
,index_dt format yymmdd10.
from proj.index_com
;
quit;

proc append base=tddata.&drv (FASTLOAD=YES)
data=work.drv;
run;

proc sql;
CONNECT TO TERADATA(tdpid=NHIPROD schema=dnhiview user=”&sysuserid@LDAP” password=”&unixpass”);
execute (diagnostic noprodjoin on for session) by teradata;
CREATE TABLE proj.drugs_rx as
select NHI.*
from connection to TERADATA
(
select drv.individual_id,
a.fill_date as fill_dt
from dodview.pharmacy_claim a
inner join
NHIPDHMMSANDBOX.&drv drv /*join to driver table*/
on a.individual_id = drv.individual_id
where a.fill_date between drv.index_dt-90 and drv.index_dt+30
and (
brand_name like any (‘%LEVOTHYROID%’,’%LEVOXYL%’,’%SYNTHROID%’,’%TIROSINT%’,’%UNITHROID%’,’%LEVOTHYROXINE%’) or
generic_name like any (‘%LEVOTHYROID%’,’%LEVOXYL%’,’%SYNTHROID%’,’%TIROSINT%’,’%UNITHROID%’,’%LEVOTHYROXINE%’)
)
group by 1,2
) nhi;
quit;