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
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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.