Helllo, I am using below SAS provided code where for my final output I am missing value for "Member_of_Group" for some users. Also, I am not receiving any error or warning message. Also, id that I am using to run the program is part of "SAS Administrator" group so it should have access to pull all information from smc - Thanks.
https://blogs.sas.com/content/sgf/2016/01/13/sas-administrators-tip-keeping-track-of-sas-users/
anyways to modify code to get all user's group member details?
Hi woo
It does not work for me either. The mduextr macro works fine, but the groups are not merged. I guess that if this code ever worked, it must have been in a older SAS version. The keys are different in version 9.5, because keyid is not in the output data set Groupmempersons_info.
Person and Groupmempersons_info must be merged by the metadata object ID, found in Groupmempersons_info as memId and in Person as objId. The result can then be merged with Logins and Email by keyId.
This requires some extra sorting. I got the variables in wrong order in output, because groups were merged in before login and email, so you might have to change the merge order. In our installation we have two logins for each person, both with and without domain name in the userID, and kept only one, because otherwise I vould have had the resulting 33772 person/group combinations duplicated for each user.
%mduextr(libref=work);
/* Merge person and groupmempersons_info by internal ObjectID
(objid in person, memid in groupmempersons_info) */
proc sort data=work.person out=work.w_person;
by objid;
run;
proc sort data=work.groupmempersons_info OUT=work.w_groupmempersons_info;
by memid;
run;
data work.w_metadata_users;
merge
work.w_person (keep=objid keyid name DisplayName title description rename=(objid=memid) in=user)
work.w_groupmempersons_info (keep=memid name rename=(name=groupname))
;
by memid;
if user;
run;
/* Merge w_metadata_users with logins and email - all on keyid */
proc sort data=work.w_metadata_users; by keyid;
run;
* Remove duplicates from logins (userid with/without domain);
data work.w_logins; set work.logins;
if index(userid,'\') = 0;
run;
proc sort data=work.w_logins; by keyid;
run;
proc sort data=work.email out=work.w_email; by keyid;
run;
data work.metadata_users (drop=keyid);
merge
work.w_metadata_users (drop=memid in=user)
work.w_logins (keep=keyid UserID)
work.w_email (keep=keyid emailAddr)
;
by keyid;
if user;
run;
proc sort data=work.metadata_users;
by name groupname;
run;
/* Blank out duplicate information */
data work.metadata_users_ready (drop=i);
set work.metadata_users;
by name;
array a [*] name DisplayName title description emailAddr UserID;
if not first.name then
do i=1 to dim(a);
a[i] = '';
end;
run;
Hi woo
It does not work for me either. The mduextr macro works fine, but the groups are not merged. I guess that if this code ever worked, it must have been in a older SAS version. The keys are different in version 9.5, because keyid is not in the output data set Groupmempersons_info.
Person and Groupmempersons_info must be merged by the metadata object ID, found in Groupmempersons_info as memId and in Person as objId. The result can then be merged with Logins and Email by keyId.
This requires some extra sorting. I got the variables in wrong order in output, because groups were merged in before login and email, so you might have to change the merge order. In our installation we have two logins for each person, both with and without domain name in the userID, and kept only one, because otherwise I vould have had the resulting 33772 person/group combinations duplicated for each user.
%mduextr(libref=work);
/* Merge person and groupmempersons_info by internal ObjectID
(objid in person, memid in groupmempersons_info) */
proc sort data=work.person out=work.w_person;
by objid;
run;
proc sort data=work.groupmempersons_info OUT=work.w_groupmempersons_info;
by memid;
run;
data work.w_metadata_users;
merge
work.w_person (keep=objid keyid name DisplayName title description rename=(objid=memid) in=user)
work.w_groupmempersons_info (keep=memid name rename=(name=groupname))
;
by memid;
if user;
run;
/* Merge w_metadata_users with logins and email - all on keyid */
proc sort data=work.w_metadata_users; by keyid;
run;
* Remove duplicates from logins (userid with/without domain);
data work.w_logins; set work.logins;
if index(userid,'\') = 0;
run;
proc sort data=work.w_logins; by keyid;
run;
proc sort data=work.email out=work.w_email; by keyid;
run;
data work.metadata_users (drop=keyid);
merge
work.w_metadata_users (drop=memid in=user)
work.w_logins (keep=keyid UserID)
work.w_email (keep=keyid emailAddr)
;
by keyid;
if user;
run;
proc sort data=work.metadata_users;
by name groupname;
run;
/* Blank out duplicate information */
data work.metadata_users_ready (drop=i);
set work.metadata_users;
by name;
array a [*] name DisplayName title description emailAddr UserID;
if not first.name then
do i=1 to dim(a);
a[i] = '';
end;
run;
Hi woo
It does not work for me either. The mduextr macro works fine, but the groups are not merged. I guess that if this code ever worked, it must have been in a older SAS version. The keys are different in version 9.5, because keyid is not in the output data set Groupmempersons_info.
Person and Groupmempersons_info must be merged by the metadata object ID, found in Groupmempersons_info as memId and in Person as objId. The result can then be merged with Logins and Email by keyId.
This requires some extra sorting. I got the variables in wrong order in output, because groups were merged in before login and email, so you might have to change the merge order. In our installation we have two logins for each person, both with and without domain name in the userID, and kept only one, because otherwise I vould have had the resulting 33772 person/group combinations duplicated for each user.
%mduextr(libref=work);
/* Merge person and groupmempersons_info by internal ObjectID
(objid in person, memid in groupmempersons_info) */
proc sort data=work.person out=work.w_person;
by objid;
run;
proc sort data=work.groupmempersons_info OUT=work.w_groupmempersons_info;
by memid;
run;
data work.w_metadata_users;
merge
work.w_person (keep=objid keyid name DisplayName title description rename=(objid=memid) in=user)
work.w_groupmempersons_info (keep=memid name rename=(name=groupname))
;
by memid;
if user;
run;
/* Merge w_metadata_users with logins and email - all on keyid */
proc sort data=work.w_metadata_users; by keyid;
run;
* Remove duplicates from logins (userid with/without domain);
data work.w_logins; set work.logins;
if index(userid,'\') = 0;
run;
proc sort data=work.w_logins; by keyid;
run;
proc sort data=work.email out=work.w_email; by keyid;
run;
data work.metadata_users (drop=keyid);
merge
work.w_metadata_users (drop=memid in=user)
work.w_logins (keep=keyid UserID)
work.w_email (keep=keyid emailAddr)
;
by keyid;
if user;
run;
proc sort data=work.metadata_users;
by name groupname;
run;
/* Blank out duplicate information */
data work.metadata_users_ready (drop=i);
set work.metadata_users;
by name;
array a [*] name DisplayName title description emailAddr UserID;
if not first.name then
do i=1 to dim(a);
a[i] = '';
end;
run;
make sense, thanks Erik, worked fine.
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.