Using Base sas 9.2 in a special tool that allows db.tablename to be the oracle table. I want to update db.DCONTRCT.CNDTFACC
from corgasb1(ACTL_DT) where CCONTID = db.DCONTRCT.CCONTID so how do I update an oracle field column using a SAS dataset column?
data corgasb2;
set corgasb1;
keep CCONTID ACTL_DT;
run;
db.DCONTRCT.CNDTFACC
Not sure if you are using the correct Oracle SQL syntax for updating.
Here's SAS/ANSI SQL with a correlated sub query (untested):
proc sql;
update db.ccontrct as a
set cndtfafacc = (select actl_dt from corgas1 as b where a.ccontid=b.ccontid)
where a.ccontid in ( select distinct ccontid from corgas1)
;
quit;
Then, if you have performance problems, you might want to upload your SAS table to Oracle first, into a temporary table.
after making a couple of minor corecction to the code:
proc sql;
update db.dcontrct as a
set cndtfacc = (select actl_dt from corgasb2 as b where a.ccontid=b.cont_id)
where a.ccontid in ( select distinct ccontid from corgasb2)
;
quit;
This puts a date in the cndtfacc.... 1/1/1960 5:34:28 AM...but the date that should have gone in was December 11 2014 The one field is a numbeer and the other field is a short date like 12/11/14 at one point I had it formated early in the code you do not see so
I will try and work it out thank you
Looks like you need to make sure actl_dt is a datetime variable not a date.
The main problem is actl_dt is actural a number as a date 20141211 and CNDTFACC is what I call a short date 12/11/14 without the time which is what I need I thought I corrected that format problem my complete code is :
******************key date*******;
data keydt;
set db.T_CONT_KEY_DT;
keep CONT_ID KEY_DT_T ACTL_DT;
Where ACTL_DT > 0;
run;
proc sort data =keydt out = keydtt;
by CONT_ID ACTL_DT;
run;
data keydttt;
set keydtt;
keep CONT_ID ACTL_DT KEY_DT_T;
Where KEY_DT_T = "K050";
run;
data keydtttt;
set keydttt;
keep CONT_ID ACTL_DT KEY_DT_T;
ACTL_DT = INPUT(PUT(ACTL_DT,8.),YYMMDD8.);
FORMAT ACTL_DT MMDDYY8.;
RUN;
I thought I FORMATED actl_dt above because when you look at the output it is the way I need the date.
data facc;
set db.DCONTRCT;
keep CCONTID CNDTFACC CNDT63;
run;
proc sort data =facc out = facct;
by CCONTID;
run;
******Joining key dates and contract*******;
Proc sql;
create table corgasb as
select distinct keydtttt.*, facct.* from
keydtttt
left join
facct
on
keydtttt.CONT_ID = facct.ccontid
;
proc sort data =corgasb out = corgasb1;
by CONT_ID ;
run;
data corgasb2;
set corgasb1;
where cont_id = "B -35553";
keep CONT_ID CCONTID ACTL_DT CNDTFACC CNDT63;
proc sql;
update db.dcontrct as a
set cndtfacc = (select actl_dt from corgasb2 as b where a.ccontid=b.cont_id)
where a.ccontid in ( select distinct ccontid from corgasb2)
;
quit;
some how I believe the 1960 date is happpening is because it is rereading the date I formated as a number again, so does someone
want to tell I do not know how to use put and input format statements
CONT_ID | KEY_DT_T | SUBJ | ACTL_DT | CCONTID | CNDTFACC | CNDT63 |
---|---|---|---|---|---|---|
B -35553 | K050 | 20141211 | B -35553 | 1/1/1960 5:34:28 AM |
5/13/2015 |
CONT_ID | KEY_DT_T | SUBJ | ACTL_DT | CCONTID | CNDTFACC | CNDT63 |
---|---|---|---|---|---|---|
B -35553 | K050 | 20141211 | B -35553 | 12/11/14 | 5/13/2015 |
thanks for the help!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.