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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.