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
Function n() counts the non missing values
data want;
set have;
array m{*} manager:;
if n(of m{*}) > 0 then Single_dummy = n(of m{*}) = 1;
if n(of m{*}) = 1 then Gender_dummy = max(of m{*});
run;
Function n() counts the non missing values
data want;
set have;
array m{*} manager:;
if n(of m{*}) > 0 then Single_dummy = n(of m{*}) = 1;
if n(of m{*}) = 1 then Gender_dummy = max(of m{*});
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.