Ok, so we need to expand the format solution.
One way would be to combine the two values that determine the outcome into a single variable and create the format for this.
Or we create multiple formats for the ID's, which I have done here, and use the putc() instead of the put() function (putc() and putn() accept expressions as formats, while put() only accepts a literal format name):
Data Dataset1;
infile datalines
dlm=","
missover
DSD;
input ID : $10.
Date_Month : mmddyy10.
Manager1 : $60.
Manager2 : $60.
Manager3 : $60. ;
format Date_Month mmddyy10. ;
datalines;
AB00046,06-30-2016,Ronald Baron,Bill F. Baron,
AB00046,07-31-2016,Ronald Baron,Bill F. Baron,
AB00046,08-31-2016,Ronald Baron,Bill F. Baron,
AB00046,09-30-2016,Ronald Baron,Bill F. Baron,
AB00046,10-31-2016,Ronald Baron,Bill F. Baron,Tim S.
AB00046,11-30-2016,Ronald Baron,Bill F. Baron,Tim S.
AB00046,12-31-2016,Ronald Baron,Bill F. Baron,Tim S.
AB00046,01-31-2017,Ronald Baron,Bill F. Baron,Tim S.
AB00046,02-28-2017,Ronald Baron,Bill F. Baron,Tim S.
AB00046,03-31-2017,Ronald Baron,Bill F. Baron,Tim S.
AB00046,04-30-2017,Ronald Baron,Bill F. Baron,Tim S.
AB00046,05-31-2017,Ronald Baron,Bill F. Baron,Tim S.
AB00046,06-30-2017,Ronald Baron,Bill F. Baron,Tim S.
AB00046,07-31-2017,Ronald Baron,Bill F. Baron,
AB00046,08-31-2017,Ronald Baron,Bill F. Baron,
AB00050,04-30-2016,
AB00050,05-31-2016,
AB00050,06-30-2016,Sharon
AB00050,07-31-2016,Sharon,Tim S.
AB00050,08-31-2016,Sharon,Tim S.
AB00050,09-30-2016,Sharon,Tim S.
;
run;
Data Dataset2;
infile datalines
dlm=","
missover
DSD;
input ID : $10.
Name_Manager : $60.
Gender ;
datalines;
AB00046,Ronald Baron,0
AB00046,Bill F. Baron,0
AB00046,Tim S.,0
AB00050,Sharon,1
AB00050,Tim S.,0
;
run;
proc sort data=dataset2;
by id;
run;
data cntlin;
set dataset2;
by id;
fmtname = strip(id) !! '_';
type = 'C';
start = name_manager;
label = put(gender,best.);
output;
if last.id
then do;
name_manager = 'other';
hlo = 'O';
label = '';
output;
end;
drop id name_manager gender;
run;
proc format cntlin=cntlin;
run;
data want;
set dataset1;
array manager {*} manager:;
do i = 1 to dim(manager);
manager {i} = putc(manager{i},strip(id) !! '_');
end;
drop i;
run;
The appended underline is necessary because your ID values end with numbers. Similarly, you would neet to prepend an underline or a character if ID started with a digit.
... View more
