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!
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.