DATA Step, Macro, Functions and more

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

Reply
Occasional Contributor
Posts: 18

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

 

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

Super User
Posts: 5,430

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

Posted in reply to GregINGov

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.

Data never sleeps
Occasional Contributor
Posts: 18

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

 

 

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

Super User
Posts: 3,254

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

Posted in reply to GregINGov

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

Occasional Contributor
Posts: 18

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

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!

 

Super User
Posts: 5,430

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

Posted in reply to GregINGov
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.
Data never sleeps
Ask a Question
Discussion stats
  • 5 replies
  • 274 views
  • 0 likes
  • 3 in conversation