how to update table data using input dataset

[bmadhu]

All,

I am new to sas base environment.

I am trying to update table data using input data. The table has primary key on 5 columns. out of 5 columns if 4 column values change then i need to update a date column.

update &d_sybname..&d_tbl set A.end_date = %SYSFUNC(DATETIME())from &d_sybname..&d_tbl as A ,reflib.&dataset as B where A.bep_fisc_yr=B.bep_fisc_yr and A.bep_app_code=B.bep_app_code and A.bep_org_code=B.bep_org_code and A.bep_pgm_ele_code!=B.bep_pgm_ele_code and A.tran_pgm_ele_code=B.tran_pgm_ele_code;

bep_fisc_yr,bep_app_code,bep_org_code,bep_pgm_ele_code,tran_pgm_ele_code are part of primary key.

but whenever bep_pgm_ele_code value changes i need to update end_date column.

sample:

load1:

bep_fisc_yr    bep_app_code   bep_org_code   bep_pgm_ele_code  tran_pgm_ele_code   end_date

2009               10                        45                      34                                   234                       null

load2:

bep_fisc_yr    bep_app_code   bep_org_code   bep_pgm_ele_code  tran_pgm_ele_code   end_date

2009               10                        45                      34                                   234                      current date

2009               10                        45                      23                                    234                       null

load3:

bep_fisc_yr    bep_app_code   bep_org_code   bep_pgm_ele_code  tran_pgm_ele_code   end_date

2009               10                        45                      34                                   234                     current date

2009               10                        45                      23                                   234                     current date

2009

10

45

13

234                    null

i tried the above query, but the issue is my input dataset (reflib.&dataset) doesn't contain any headers.

in this case how can i compare column values in where statement.

Thanks,

Madhu

[Steelers_In_DC]

I'm guessing there will be more elegant solutions than this, but here's one way to do it:

data have;

infile cards dsd;

input bep_fisc_yr    bep_app_code   bep_org_code   bep_pgm_ele_code  tran_pgm_ele_code   end_date$ ;

cards;

2009,10,45,34,234,null

2009,10,45,34,234,current

2009,10,45,23,234,null

2009,10,45,34,234,current

2009,10,45,23,234,current

2009,10,45,13,234,null

;

run;

data want;

set have;

lbep_f = lag(bep_fisc_yr);

lbep_a = lag(bep_app_code);

lbep_o = lag(bep_org_code);

lbep_p = lag(bep_pgm_ele_code);

ltran = lag(tran_pgm_ele_code);

if _N_ > 1 then do;

if lbep_f ne bep_fisc_yr then lbep_f_c = 1;

if lbep_a ne bep_app_code then lbep_a_c = 1;

if lbep_o ne bep_org_code then lbep_o_c = 1;

if lbep_p ne bep_pgm_ele_code then lbep_p_c = 1;

if ltran ne tran_pgm_ele_code then ltran_c = 1;

end;

if sum(lbep_f_c,lbep_a_c,lbep_o_c,lbep_p_c,ltran_c) < 3 then change = 'No Change';

    else change = 'Change';

run;