Hi I have a data set, Have, where Manager1, Manager2, and Manager3 show gender of these three managers, for unique IDs. '0' stands for a Male and '1' for a Female manager. Missing observations are displayed as '.'. I want another data set with two new columns of Dummy Variables. One column should take value '0' when on a particular date, more than one managers are serving (multiple observations in a row). The value should be '1' if only one/single manager (one observation per row) is there on a particular date. Second column should specify the gender of that single manager on a particular date. So if on a date, only one manager's observation is available, the new column should take value '1', if the manager is a Female, or '0' if Male. If multiple observtions are there per row, then this new column should take missing value i.e. '.'. Please see below a sample of "Have" and "Want" data sets. Data Have;
infile datalines
dlm=","
missover
DSD;
input ID : $10.
Date_Month : mmddyy10.
Manager1
Manager2
Manager3 ;
format Date_Month mmddyy10. ;
datalines;
AB00046,11-30-2016,.,.,.
AB00046,12-31-2016,0,.,.
AB00046,01-31-2017,0,0,1
AB00046,02-28-2017,.,0,.
AB00048,11-30-2016,1,.,0
AB00048,12-31-2016,1,.,0
AB00048,01-31-2017,1,0,0
AB00048,02-28-2017,1,0,.
AB00050,11-30-2016,.,.,1
AB00050,12-31-2016,.,0,1
AB00050,01-31-2017,1,0,1
AB00050,02-28-2017,.,0,.
;
run; Data Want;
infile datalines
dlm=","
missover
DSD;
input ID : $10.
Date_Month : mmddyy10.
Manager1
Manager2
Manager3
Single_dummy
Gender_dummy;
format Date_Month mmddyy10. ;
datalines;
AB00046,11-30-2016,.,.,.,.,.
AB00046,12-31-2016,0,.,.,1,0
AB00046,01-31-2017,0,0,1,0,.
AB00046,02-28-2017,.,0,.,1,0
AB00048,11-30-2016,1,.,0,0,.
AB00048,12-31-2016,1,.,0,0,.
AB00048,01-31-2017,1,0,0,0,.
AB00048,02-28-2017,1,0,.,0,.
AB00050,11-30-2016,.,.,1,1,1
AB00050,12-31-2016,.,0,1,0,.
AB00050,01-31-2017,1,0,1,0,.
AB00050,02-28-2017,.,0,.,1,0
;
run; Kindly guide which code will fulfill this purpose. Thanks
... View more