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
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
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
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.