BookmarkSubscribeRSS Feed
bmadhu
Calcite | Level 5

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

3 REPLIES 3
PGStats
Opal | Level 21

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

PG
Ksharp
Super User

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
Calcite | Level 5

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.

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 649 views
  • 0 likes
  • 3 in conversation