Hi All,
I need to have some solution for the below situtuion which i have come acorss recently. I have to find the latest reporting division value based on the month for each employee and it should be updated all the other data in the same column.
For Example. The latest reporting division for the employee 85766 is 'C' and the value should be updated for the rest of months. which means all the reporting division value is 'C' for 85776 employee.
Please see the dataset below.
Emp_NO REPORTING_DIVISION DIVISIONAL_CODE MONTH
85776 B B 201201
85776 B B 201202
85776 B B 201203
85776 B B 201204
85776 B B 201205
85776 B B 201206
85776 B B 201207
85776 B B 201208
85776 B B 201209
85776 B B 201210
85776 B B 201211
85776 B B 201212
85776 B B 201301
85776 B B 201302
85776 B B 201303
85776 B B 201304
85776 B B 201305
85776 B B 201306
85776 B B 201307
85776 B B 201308
85776 B B 201309
85776 B B 201310
85776 B B 201311
85776 B B 201312
85776 B B 201401
85776 B B 201402
85776 B B 201403
85776 B B 201404
85776 B B 201405
85776 B B 201406
85776 C C 201407
85776 C C 201408
85776 C C 201409
86322 B B 201201
86322 B B 201202
86322 B B 201203
86322 B B 201204
86322 B B 201205
86322 B B 201206
86322 B B 201207
86322 B B 201208
86322 B B 201209
86322 B B 201210
86322 B B 201211
86322 B B 201212
86322 B B 201301
86322 B B 201302
86322 B B 201303
86322 B B 201304
86322 C C 201407
86322 C C 201408
86322 C C 201409
Thanks in advance.
Kannan.
Something like this might work:
Proc sort data=have; By Emp_no Month;run;
data temp;
set have;
by Emp_no;
if last.Emp_no;
run;
data want;
update have temp (drop= month divisional_code); /* if you want to update the divisional code as well do not include it in the drop */
by emp_no;
run;
You didn't post your output yet.
Assuming the data has already been sorted.
data have; input Emp_NO REPORTING_DIVISION $ DIVISIONAL_CODE $ MONTH ; cards; 85776 B B 201201 85776 B B 201202 85776 B B 201203 85776 B B 201204 85776 B B 201205 85776 B B 201206 85776 B B 201207 85776 B B 201208 85776 B B 201209 85776 B B 201210 85776 B B 201211 85776 B B 201212 85776 B B 201301 85776 B B 201302 85776 B B 201303 85776 B B 201304 85776 B B 201305 85776 B B 201306 85776 B B 201307 85776 B B 201308 85776 B B 201309 85776 B B 201310 85776 B B 201311 85776 B B 201312 85776 B B 201401 85776 B B 201402 85776 B B 201403 85776 B B 201404 85776 B B 201405 85776 B B 201406 85776 C C 201407 85776 C C 201408 85776 C C 201409 86322 B B 201201 86322 B B 201202 86322 B B 201203 86322 B B 201204 86322 B B 201205 86322 B B 201206 86322 B B 201207 86322 B B 201208 86322 B B 201209 86322 B B 201210 86322 B B 201211 86322 B B 201212 86322 B B 201301 86322 B B 201302 86322 B B 201303 86322 B B 201304 86322 C C 201407 86322 C C 201408 86322 C C 201409 ; run; data want; do until(last.Emp_NO); set have; by Emp_NO; end; _X=REPORTING_DIVISION; do until(last.Emp_NO); set have; by Emp_NO; REPORTING_DIVISION=_X; output; end; drop _X; run;
Xia Keshan
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.