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;
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.