Manually Creating Report Distribution Data Sets in WRSDIST

[JDMTX]

I am in the process of automating the build of report distribution data sets for the WRSDIST library based on the various Groups and Persons who reside in my metadata server repository.

1. Are there any default pre-existing xml maps for an extract of the IdentityGroup and Person metadata? The basic proc metadata statement is fairly straight forward but I have no experience in converting the xml data into data sets that I can manipulate to build my report distribution data sets.

2. Are there any pre-existing macros that I can use, salvage or repurpose to extract the group and person information?

3. Wanted to verify that the construct of a WRSDIST data set consists of a user name and distribution channel. Visual inspection seemed to confirm this but it is safer to ask someone who has been down this road instead of re-inventing the wheel.

The reason that I am taking this approach is that I authorize user access to reports and data via Active Directory Security Groups that are imported/synchronized on a daily basis.

Many Thanks

JM

[julierd]

Hi JM,

I don't have any first hand knowledge of this process myself, however, I have inquired about it for you and learned the following:

- It is possible to export contacts from Outlook to Excel (http://office.microsoft.com/en-us/outlook-help/export-contacts-from-outlook-to-excel-HA001096422.aspx) and then convert the Excel worksheet into a SAS data set.

- The two required columns for the WRSDIST data set are: EMAIL and CHANNEL. Other columns may correspond of by-groups. You do not need to fill the EMAIL column if not appropriate, but it should be present.

I hope this is helpful to you.

Julie

[JDMTX]

When I posted my original question, I had hoped that another user on this forum had implemented a distribution list solution using the data found in the metadata repository. In the end, I wrote the code myself and am posting it here in case anyone else is interested in the approach. I have tested this approach in my environment (Windows 2003 Server) and it works but must warn that the included code has been sanitized to remove user id's, passwords, server names, liburi’s, etc. Note that you will need to modify one or two metadata settings in Management Console but the log provides enough information to figure out what authorization changes are required. I will post references to the papers and people at technical support that steered me in the right direction. The goal at the end of the day was and is to automate the maintenance of my WRS distribution lists by having them driven by the user and groups stored in the metadata repository.

options metaserver="app.server.com"
metaport=8561
metaprotocol=bridge
metarepository="Foundation"
metauser="xxxxxxxxxx"
metapass="xxxxxxxxxx";

/* Section Begin - Identify Repository Types Section */
/* */
/* Extract list of all repository types from the meta data repository */
/* Checkstep that can be dropped */
filename types "M:\Staging Items\temp\types.xml" encoding="utf-8";

proc metadata in='

SAS


'
header=full
out=types;
run;

/* Section Begin - Person-Group Section */
/* */
/* Extract the Person Type from the meta data repository */
/* Use an xml map to extract each unique group, person and e-mail id */
/* Remove null e-mail observations from transaction data set */
/* Eliminate all groups from transaction data set not managed from ad */
filename outprsn "M:\Staging Items\temp\users.xml" encoding="utf-8";

proc metadata in='
$METAREPOSITORY
Person
SAS
257
'
header=full
out=outprsn;
run;

filename SXLELIB 'M:\Staging Items\temp\users.xml';
filename SXLEMAP 'M:\Staging Items\temp\UserGroupAssignment.map';
libname SXLELIB xml xmlmap=SXLEMAP access=READONLY;

proc contents data=SXLELIB.UserGroups varnum;
run;

data usergroups;
set SXLELIB.UserGroups;
if Email_Id = "" then delete;
if substr(IdentityGroup_Name,1,12) ne "SAS" then delete;
run;

proc sort data=usergroups;
by email_id;
run;


/* Section Begin - E-Mail Address Section */
/* */
/* Extract the E-Mail Type from the meta data repository */
/* Use an xml map to extract each unique e-mail id and e-mail address */
/* Remove e-mail type of subscriber from transaction data set */
filename outmail "M:\Staging Items\temp\email.xml" encoding="utf-8";

proc metadata in='
$METAREPOSITORY
Email
SAS
257
'
header=full
out=outmail;
run;

filename email 'M:\Staging Items\temp\email.xml';
filename SXLEMAP 'M:\Staging Items\temp\UserEmailAssignment.map';
libname email xml xmlmap=SXLEMAP access=READONLY;

proc contents data=email.Email varnum;
run;

data email;
set email.email;
if email_emailtype="Subscriber" then delete;
run;

proc sort data=email;
by email_id;
run;

/* Section Begin - Build Distribution Data Set */
/* */
/* Merge email and user/group data sets using the e-mail id */
/* Delete an users with a null e-mail address */
/* For this example only retain on set of users based upon ad membership */
/* Register data set with metadata server to appear in wrs recipient list */
/* Must use work around provided in SAS Problem Note 40231 */
libname wrsdist "D:\SAS\BIserver\Lev1\SASApp\Data\wrsdist";

data email_group_listing;
merge usergroups email;
by email_id;
if IdentityGroup_Id = "" then delete;
rename email_address=email;
run;

data wrsdist.DistributionList1;
attrib email length=$1024;
attrib channel length=$1024;
set email_group_listing;
if IdentityGroup_Name = "SAS Report Users";
channel="";
keep email channel;
run;

libname wrsdist meta repname="Foundation" LIBURI="XXXXXXXX.YYYYYYYY" metaout=data;

/* Required Statement - Problem Note 40231: ERROR: Libname _PMTA01_ is not assigned when running PROC METALIB */
libname _PMTA01_ (wrsdist);

proc metalib;
omr (liburi="XXXXXXXX.YYYYYYYY");
*noexec;
report;
run;

libname wrsdist clear;