Update column data in existing SAS table from Extrenal table (alreday have connection to SAS)

[JJP1]

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

[Patrick]

@JJP1 

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.

[JJP1]

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;

[Patrick]

@JJP1 

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.

[JJP1]

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"

[JJP1 · Accepted solution]

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;

[Patrick]

Please post the SAS log where we see the SQL and Error in the log

[JJP1]

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):  ;

[JJP1]

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

[Patrick]

@JJP1 

You found a solution. That's good to hear. Sorry, got really busy last week so couldn't spend much time for the forums.