Identifying single or multiple observations per row

Saba1 — Sun, 09 Jun 2019 03:07:29 GMT

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

Re: Identifying single or multiple observations per row

PGStats — Sun, 09 Jun 2019 03:48:24 GMT

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;

Re: Identifying single or multiple observations per row

Saba1 — Tue, 11 Jun 2019 23:37:30 GMT

@PGStats: Thank you very much. This code works very well.

topic Identifying single or multiple observations per row in SAS Programming

Identifying single or multiple observations per row

Re: Identifying single or multiple observations per row

Re: Identifying single or multiple observations per row