how to update a column

[bmadhu]

All,

I worked as Informatica developer and I am new to SAS base environment.

in the existing code data is being inserted or updated using Modify statement and primary key( which is combination of 5 keys).

col1,col2,col3,col4,col5 are part of primary key. col6 is date column.

whenever new value comes in for col5 i need to update col6 of the previous record with current date.

req :

load#1:

col1  col2  col3  col4  col5  col6

12      6        4     5       8      null

load#2:

col1  col2  col3  col4  col5  col6

12      6        4     5       8      current date

12      6        4     5       7       null

load#3:

col1  col2  col3  col4  col5  col6

12      6        4     5       8      current date

12      6        4     5       7       current date

12      6        4     5       6       null

how can we implement above scenario using modify statement.

Thanks,

Madhu

[PGStats]

I think it is easier and safer to do this operation with SQL :

data load1;

input col1  col2  col3  col4  col5;

datalines;

12      6        4     5       6

;

data load2;

input col1  col2  col3  col4  col5  col6 :yymmdd10.;

format col6 yymmdd10.;

datalines;

12      6        4     5       8      2015-06-14

12      6        4     5       7      .

;

proc sql;

update load2 as a

set col6 = today()

where

     exists (

         select * from load1

         where col1=a.col1 and col2=a.col2 and col3=a.col3 and col4=a.col4) and

     col6 is missing;

insert into load2 (col1, col2, col3, col4, col5, col6)

select col1, col2, col3, col4, col5, . from load1;

select * from load2;

quit;

PG

[Ksharp]

If I understood .

data have;

set have end=last;

if last then col6=today();

end;

proc append base=have data=new force;run;

Xia Keshan

[bmadhu]

All, Thanks for your replies.

here i am adding more information.

existing code:

data &stg_sybname..stg_tbl.;

    modify &stg_sybname..stg_tbl. (DBINDEX=&ikey.) reflib.&dataset (DROP=insrt_date updt_date)

    UPDATEMODE=NOMISSINGCHECK;

    by &bkey;

    select (_IORC_);

    when (%SYSRC(_SOK)) do;

    updt_date = %SYSFUNC( DATETIME());

    replace;

    end;

    when (%SYSRC(_DSENMR)) do;

    insrt_date = %SYSFUNC( DATETIME());

    updt_date = '01JAN1900:00:00:00'dt;

    output;

    _error_=0;

    end;

    when (%SYSRC(_DSEMTR)) do;

    put 'ERR' 'OR: Duplicate Values on transaction dataset';

    stop;

    end;

    otherwise do;

    put 'ERR' 'OR: Unknown IO ';

    stop;

    end;

    end;

    run;

    %END;

above code is working fine for modifying data using bkey.

my approach to the current req:

i tried to update col6 using %SYSRC(_SOK) whenever col5 changes. it didn't work since col5 is part of primary key, any change to col5 value, it treats as new record.