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.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.