topic Re: how to update a column in SAS Programming

how to update a column

bmadhu — Mon, 15 Jun 2015 19:34:07 GMT

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

Re: how to update a column

PGStats — Tue, 16 Jun 2015 02:14:51 GMT

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;

Re: how to update a column

Ksharp — Tue, 16 Jun 2015 14:02:11 GMT

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

Re: how to update a column

bmadhu — Tue, 16 Jun 2015 14:57:24 GMT

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;

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.