The new column called "Type" got created on extrenal oracle table with created data on 04SEP2019:13:19:15.367591.
so we alreday have connection to SAS for above mentioned oracle table.
so i just updated metedata.i can see new column in SAS DI.
but how to write code the extract the data under new column "Type" in SAS please from 04SEP2019 to today's date.
please help
Iam trying to update single column in SAS using oracle pass through option.
So for this to achive iam using Sql Join Transformation :Pass thorugh option in SAS DI job
under join node below code is getting generated which is working fine.
proc sql;
connect to ORACLE
(
READBUFF=456 PATH=SIT AUTHDOMAIN='Auth'
);
create table work.W6SZW2W as
select
ID length = 8
format = 20.
informat = 20.
label = 'ID',
Source length = 30
label = 'Source '
from connection to ORACLE
(
select
PPP.ID as PPID,
PPP.Source
from
UU.PPP PPP
where
PPP.Source <> ' '
);
i am expecting to get the below code to be generated by Update Transformation(under SQL ).
proc sql;
update DDD.SASDataset
set
Source= (
select
W6SZW2W.ID length = 8
format = 20.
informat = 20.
label = 'ID'
from
work.W6SZW2W as W6SZW2W
where
W6SZW2W.ID = SASDataset.ID
)
;
quit;
But somehow options that iam selecting in update transformation is getting generated below code(error where SASDataset.W6SZW2W.ID ).due to which SAS
job is getting failed syaing syntax error.kindly help please.
proc sql;
update DDD.SASDataset
set
Source= (
select
W6SZW2W.ID length = 8
format = 20.
informat = 20.
label = 'ID'
from
work.W6SZW2W as W6SZW2W
where
W6SZW2W.ID = SASDataset.W6SZW2W.ID
)
;
quit;
Your question lacks some clarity so answering here based on some assumptions.
You could use the Extract transformation with a where clause like:
create_date>='04SEP2019:00:00:00'dt
If that's a DIS job then I don't believe you really want to use some hard coded date string - but you haven't told us where you get the lower boundary date from for your selection. Whatever you're going to do in reality just make sure that you use "something" in the where clause which SAS will treat as datetime value and which can get passed to the database for processing.
If you're going to use a SAS variable instead of a fixed date string then make sure that this variable has a format of DATETIME20. applied.
Hi @Patrick ,
sorry for confussion.please find below.
I have a DI job which will run on daily basis by extracting the data from oracle source based on date column and loads the data into final table using "Append to existing" table loader option.
So i neeed to bring new column "Type" from oracle to SAS and update only Type column with appropriate values from source.
Iam trying something like below.but not working.please help.(kindly note that there are other columns already loaded with data for this date so i can not recreate as iamare suing "Append to existing " option please)
proc sql;
connect to ORACLE ( READBUFF=222 PATH=FFF.GG AUTHDOMAIN='HHH' )
from connection to ORACLE ;
update aa.KKK
set Type =(select Type from aaa.JJJ
where
TRUNC(CREATION_DATETIME)
BETWEEN TRUNC(to_date('03SEP2019:00:00:00','DDMONYYYY:HH24:MI:SS')) and
TRUNC(to_date('15SEP2019:23:59:59','DDMONYYYY:HH24:MI:SS')) ) ;
quit;
1. Your Oracle Update must be within an EXECUTE() block else SAS won't know that this is code which you want to execute against Oracle.
2. Your sub-query needs to return a single value (or NULL) per row in your master table. I doubt that the where clause as you've defined it would do this. I've added to below code some made-up logic using a variable called key - that's likely not what you need but it should give you the idea.
3. TRUNC() will align datetime values to the beginning of the day. There is no need to do this for your between clause to work.
Below code won't be the final solution but it should already be closer to what you need.
proc sql;
connect to ORACLE ( READBUFF=222 PATH=FFF.GG AUTHDOMAIN='HHH' );
execute (
update aa.kkk
set type =(
select type
from aaa.jjj
where
kkk.key=jjj.key
and
jjj.creation_datetime
between to_date('03sep2019:00:00:00','ddmonyyyy:hh24:mi:ss')
and to_date('15sep2019:23:59:59','ddmonyyyy:hh24:mi:ss')
)
)
by oracle;
disconnect from oracle;
quit;
If developing pass-through SQL then I normally use an client like SQL Developer or DBeaver and only once I've got working SQL copy/paste it into a SAS SQL EXECUTE() block. That makes it for me easier to develop and test the DB SQL code.
Hi @Patrick ,
Actually i need to update the SAS dataset not the oracle table please.
i think the mentioned code in previous post will work to update the oracle tables ?please correct me if iam wrong.
Also i tried the code and iam keep on getting the message as "Oracle table/View does not exist"
Iam trying to update single column in SAS using oracle pass through option.
So for this to achive iam using Sql Join Transformation :Pass thorugh option in SAS DI job
under join node below code is getting generated which is working fine.
proc sql;
connect to ORACLE
(
READBUFF=456 PATH=SIT AUTHDOMAIN='Auth'
);
create table work.W6SZW2W as
select
ID length = 8
format = 20.
informat = 20.
label = 'ID',
Source length = 30
label = 'Source '
from connection to ORACLE
(
select
PPP.ID as PPID,
PPP.Source
from
UU.PPP PPP
where
PPP.Source <> ' '
);
i am expecting to get the below code to be generated by Update Transformation(under SQL ).
proc sql;
update DDD.SASDataset
set
Source= (
select
W6SZW2W.ID length = 8
format = 20.
informat = 20.
label = 'ID'
from
work.W6SZW2W as W6SZW2W
where
W6SZW2W.ID = SASDataset.ID
)
;
quit;
But somehow options that iam selecting in update transformation is getting generated below code(error where SASDataset.W6SZW2W.ID ).due to which SAS
job is getting failed syaing syntax error.kindly help please.
proc sql;
update DDD.SASDataset
set
Source= (
select
W6SZW2W.ID length = 8
format = 20.
informat = 20.
label = 'ID'
from
work.W6SZW2W as W6SZW2W
where
W6SZW2W.ID = SASDataset.W6SZW2W.ID
)
;
quit;
Please post the SAS log where we see the SQL and Error in the log
Hi @Patrick ,please find below Error in log.please help
MPRINT(ETLS_SQL_UPDATE): proc sql;
^L10 The SAS System 16:57 Tuesday, September 17, 2019
NOTE: Line generated by the invoked macro "ETLS_SQL_UPDATE".
308 where W6SZW2W.ID = sasdataset.W6SZW2W.ID
_
22
76
308 ! ) ; quit; %rcSet(&syscc);
MPRINT(ETLS_SQL_UPDATE): update DDD.sasdataset set Source= ( select W6SZW2W.Source length = 30 label =
'Source ' from work.W6SZW2W as W6SZW2W where W6SZW2W.ID =
sasdataset.W6SZW2W.ID ) ;
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, ), *, **, +, -, /, <, <=, <>, =, >, >=, AND, EQ, EQT, EXCEPT,
GE, GET, GROUP, GT, GTT, HAVING, INTERSECT, LE, LET, LT, LTT, NE, NET, NOT, OR, OUTER, UNION, ^, ^=, |, ||, ~, ~=.
ERROR 76-322: Syntax error, statement will be ignored.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
MPRINT(ETLS_SQL_UPDATE): quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 45.59k
OS Memory 8740.00k
Timestamp 17/09/2019 16:57:50 o'clock
Page Faults 3
Page Reclaims 5
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 2
Block Input Operations 0
Block Output Operations 0
MPRINT(ETLS_SQL_UPDATE): ;
Hi @Patrick ,
Iam able to run the job and update a single column in SAS using oracle table by making use of update transfomation.
Thanks for help
You found a solution. That's good to hear. Sorry, got really busy last week so couldn't spend much time for the forums.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.