How to update oracle field column using a SAS dataset column?

[GregINGov]

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

[LinusH]

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;

https://support.sas.com/documentation/cdl/en/sqlproc/69049/HTML/default/viewer.htm#p0z9p6hclwnhxin1m...

Then, if you have performance problems, you might want to upload your SAS table to Oracle first, into a temporary table.

[GregINGov]

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

[SASKiwi]

Looks like you need to make sure actl_dt is a datetime variable not a date. 

[GregINGov]

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

This is what I get with the above code

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

this is what I want

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!

[LinusH]

It seems that you have fixed actl_dt, just verify by looking into work.keydttt.

But I think that at least in the Oracle libname that cndtfacc is defined as datetime. You should be able to verify this on the untouched records in dbcontract.