BookmarkSubscribeRSS Feed
bmadhu
Calcite | Level 5

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

1 REPLY 1
Steelers_In_DC
Barite | Level 11

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;

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • 300 views
  • 0 likes
  • 2 in conversation