how to update table data using input dataset

bmadhu — Wed, 17 Jun 2015 14:39:16 GMT

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

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

Re: how to update table data using input dataset

Steelers_In_DC — Wed, 17 Jun 2015 15:37:40 GMT

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;

topic Re: how to update table data using input dataset in SAS Programming

how to update table data using input dataset

Re: how to update table data using input dataset