Created
February 4, 2015 21:15
-
-
Save rpardee/ed4cfc85f23081fd1a44 to your computer and use it in GitHub Desktop.
sas -> teradata temp table: why won't teradata do this join?
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
* Write db commands to the log. ; | |
options | |
fullstimer | |
sastrace = ',,,d' | |
sastraceloc = saslog | |
; | |
%let my_server = EDW_PROD1 ; | |
%let my_schema = SB_GHRI ; | |
%let td_goo = user = "&username@LDAP" | |
pwd = "&password" | |
server = "&my_server" | |
schema = "&my_schema" | |
connection = global | |
; | |
/* | |
mode = teradata | |
*/ | |
* This lib exists only to write temp dsets to the server (thus, the dbmstemp setting). ; | |
libname temp_td teradata &td_goo dbmstemp = yes ; | |
* This one is for reading substantive data. ; | |
libname real_td teradata &td_goo ; | |
* Make a fake set of Medical Record Numbers. ; | |
data fake_mrns; | |
length mrn $ 10 ; | |
do col1=1 to 1000 ; | |
mrn = substr(put(md5(col1), hex32.), 1, 10) ; | |
output; | |
end; | |
drop col1 ; | |
run; | |
* Move these guys up to TD in a temp table ; | |
proc append base = temp_td.my_cohort data = fake_mrns ; | |
run; | |
proc sql ; | |
* Now we join temp to substantive--can we get the db to do it? ; | |
create table bloob as | |
select u.* | |
from real_td.utilization as u INNER JOIN | |
temp_td.my_cohort as c | |
on u.mrn = c.mrn | |
; | |
quit ; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment