user info from smc

[woo]

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?

[ErikLund_Jensen · Accepted solution]

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;

[ErikLund_Jensen]

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;

[woo]

make sense, thanks Erik, worked fine.