Architecting, installing and maintaining your SAS environment

Create users/groups in SAS from SQL crosswalk table - sample script?

Accepted Solution Solved
Reply
Contributor
Posts: 73
Accepted Solution

Create users/groups in SAS from SQL crosswalk table - sample script?

I have a "crosswalk" table in SQL, where I map my users to various "data areas".  I want to load this table in SAS...and then run a process against the table to automate the creation of users/groups (and populate groups) in SAS.  I see the sample for doing this from Active Directory...but not from a table that is loaded in SAS.  Is anyone doing this?  If so, do you have a script that you would mind sharing?  I think that I need to use the cononical tables, but I don't understand how I go from SAS to cononical table to SAS user/group.  By the way, I am new to SAS, so it is possible that I am missing/misunderstanding the obvious. :-)

 

Thanks,

Ricky


Accepted Solutions
Solution
‎07-19-2017 11:39 AM
Contributor
Posts: 73

Re: Create users/groups in SAS from SQL crosswalk table - sample script?

Posted in reply to JuanS_OCS

First, let me thank @JuanS_OCS and @LinusH for your help.  With your help, I was able to create a solution that works for my setup.

 

So, here is my setup.  I work in Higher Education, so almost everything in my data maps to college codes and department codes.  However, I did not have a data sources that tied individuals to a college/department...and I needed this, as we plan to use SAS' row-level security.  In order to create this, I have several pieces...but, I wanted it all to work from Active Directory.  So, I created the following:

 

Active Directory:  I have an OU setup with 100 or so groups in it.  Each group contains any number of users.

SQL Server:  I have a job in SQL Server that is setup to run at the top of every hour.  This job essentailly gets my group membership for all group in my OU.  The last step of this job is to ETL the data to Oracle, which is where my crosswalk table exists.

Oracle:  In Oracle, I have my crosswalk table.  This crosswalk table maps each of my AD groups to colleges and departments.  A group can map to one or more colleges and one or more departments.  By have the crosswalk table at the group level, instead of having it at the user level, it is much easier to maintain.

SAS:  I have a job setup in SAS that joins the crosswalk table and the membership table (taking into account some speacial groups that gives members access to everything) and then pushes this data to Hadoop and then to LASR.  The result is a table that contains every user and every combination of college/department that he/she has access to.

 

Now, for my row-level security, as I mentioned, most (if not all) of my data contains columns for College Code and Department Code.  So, this is what I based by row-level security on.  By doing this, it means that I need a SAS Group for each College Code and each College Code || Department Code combination (as a department can belong to multiple colleges).

 

This is where my crosswalk table comes into play.  My crosswalk table contains exactly what I need...every College Code and College Code || Department Code combination.  I can base my SAS Groups off of this information...and my crosswalk table also contains the members for each group.  Perfect!  But, the problem was...how do I go from crosswalk table (which is a table that I have loaded into LASR) to SAS Groups and Users (by the way, my SAS enviroronment is setup to authenticate against Active Directory).  So, I started asking question in this post and started building a script.  I ended up with the below script setup as a Data Integration Studio job (with user written note).  This allowed me to then schedule this script after HDFS and LASR have both loaded, as I run this process hourly.

 

So, the results are this...I can now maintain all of my users and groups via AD.  I simply add a new user to an AD group, and the process with recognize that user and add him/her to SAS.  It will add my person record and add him/her to the appropriate groups.  The same thing goes for groups.  This process will also take care of my updates and deletes...which is exactly what I was looking for.  So, now for the script.  Enjoy.  I hope that this saves others some work.

 

A couple notes about the script:

  • You will notice that I have created a new MDUCHGV macro.  This is due to the following:  http://support.sas.com/kb/49/002.html
  • You will notice that in one of the proc sql steps below, I create a veiw.  This is due to the above...I was trying to get around that warning without having to modify the macro.  Since the macro has been modified, this could be cleaned up.
  • In my DI Studio job, I have two notes...User Written (which contains the code below) and Return Code Check (which checks the return code of the User Written node and emails me the results - Outlook rule files away the success emails, and flags any failures).
  • With my setup, I am only maintainging accounts/groups for my "general" users.  All admin accounts and power users are being handled manually (as these users do not appear in my crosswalk).  At some later time, I may modify the script to read info for my admins and power users directly from AD.  I am using my exclusions for these accounts...so that the script skips over them.

 

If anyone has any suggestions on how to improve this script, please post.  I am always looking for suggestions, as I am just getting stated in SAS.

 

_________________________________________________________

 

/*---- Start of User Written Code  ----*/

/* Use the edited version of the %MDUCHGV macro */
/* to prevent PROC SQL truncation warnings, per */
/* SAS Note 49002.                              */
%include '/data/sasdata/admin/mduchgv.sas';

/* Get METAServer from automatic macro variables */
%let var_METAServer = &METASERVER;

/*-------------------------------------------------------
* Define system options to connct to the metadata server.
*-------------------------------------------------------*/
options metaserver=your.sas.server.here 
        metaport=8561
        metauser="unrestricted_user_here@saspw"
        metapass="password_for_above_user__should_encode_this"
        metaprotocol=BRIDGE
        metarepository=Foundation;

/* Define the library where the extracted crosswalk info will be stored. */
libname XWALK "/data/sasdata/admin/XWALKextract";
%rcSet(&syslibrc);

/* Define the library where the extracted metadata info will be stored. */
libname META "/data/sasdata/admin/METAextract";
%rcSet(&syslibrc);

/* Define the library where the change data sets will be stored. */
libname UPDATES "/data/sasdata/admin/METAupdates";
%rcSet(&syslibrc);

/* Define the library where the Exclusions data sets will be stored. */
libname EXCLUDE "/data/sasdata/admin/Exclusions";
%rcSet(&syslibrc);

/* Clean up if last execute left tables in libraries. */
proc datasets library=XWALK memtype=data kill;
proc datasets library=META memtype=data kill;
proc datasets library=UPDATES memtype=data kill;
proc datasets library=EXCLUDE memtype=data kill;
run;
quit;

/* Create canonical tables for XWALK. */
%MDUIMPC (LIBREF=XWALK, MAKETABLE=1);
%rcSet(&syserr);

/* Define the library where the crosswalk info resides. */
LIBNAME LASRLIB SASIOLA  TAG=VAPUBLIC  PORT=10031 HOST="your_sas_host_name_here";
%rcSet(&syslibrc);

/* Populate XWALK.person. */
proc sql;
  INSERT INTO XWALK.person (keyid, name, displayname, description)
  SELECT DISTINCT NETID AS keyid, NETID AS name, NETID AS displayname, 'my (RLS) - Auto-Synched User' AS description
  FROM LASRLIB.my_SECURITY_CROSSWALK;
quit;
%rcSet(&sqlrc);

/* Populate XWALK.grpmems. */
proc sql;
  INSERT INTO XWALK.grpmems (grpkeyid, memkeyid)
  SELECT DISTINCT STRIP(LEFT(COLL_CODE))||STRIP(LEFT(COLL_DEPT)) AS grpkeyid, NETID AS memkeyid
  FROM LASRLIB.my_SECURITY_CROSSWALK
  WHERE COLL_CODE IS NOT NULL;
quit;
%rcSet(&sqlrc);

/* Populate XWALK.logins. */
proc sql;
  INSERT INTO XWALK.logins (keyid, userid, authdomkeyid)
  SELECT DISTINCT NETID AS keyid, NETID AS userid, 'DefaultAuth' as authdomkeyid
  FROM LASRLIB.my_SECURITY_CROSSWALK;
quit;
%rcSet(&sqlrc);

/* Populate XWALK.idgrps. */
proc sql;
  CREATE VIEW aview AS
  SELECT DISTINCT STRIP(LEFT(COLL_CODE))||STRIP(LEFT(COLL_DEPT)) AS keyid, SUBSTR((STRIP(LEFT(COLL_CODE))||STRIP(LEFT(COLL_DEPT))), 1, MIN(60, LENGTH(STRIP(LEFT(COLL_CODE))||STRIP(LEFT(COLL_DEPT))))) LENGTH=60  AS name,
  CASE
    WHEN COLL_DEPT IS NULL THEN SUBSTR((LEFT('my - (RLS) - ')||STRIP(TRIM(COLL_DESC))), 1, MIN(255, LENGTH(LEFT('my - (RLS) - ')||STRIP(TRIM(COLL_DESC)))))
    ELSE SUBSTR((LEFT('my - (RLS) - ')||STRIP(LEFT(COLL_DESC))||' - '||STRIP(LEFT(COLL_DEPT))), 1, MIN(255, LENGTH(LEFT('my - (RLS) - ')||STRIP(LEFT(COLL_DESC))||' - '||STRIP(LEFT(COLL_DEPT)))))
  END LENGTH=255 as displayname,
  CASE
    WHEN COLL_DEPT IS NULL THEN 'College'
    ELSE 'Department'
  END as description
  FROM LASRLIB.my_SECURITY_CROSSWALK
  WHERE COLL_CODE IS NOT NULL;
 
  INSERT INTO XWALK.idgrps (keyid, name, displayname, description)
  SELECT DISTINCT keyid, name, displayname, description
  FROM aview;
quit;
%rcSet(&sqlrc);

/* Populate XWALK.authdomain. */
proc sql;
  INSERT INTO XWALK.authdomain (keyid, authdomname)
  VALUES ('DefaultAuth', 'DefaultAuth');
quit;
%rcSet(&sqlrc);

/* Define exclusions for NETIDs - these are the NETIDs that have been added manually to SAS. */
/* This would account for power users and above - no need to worry about local SAS accounts. */
PROC SQL;
  CREATE TABLE EXCLUDE.exclusion_rules
    (  tablename CHAR(255),
       filter    CHAR(255)
    );
  INSERT INTO EXCLUDE.exclusion_rules
    VALUES('person', 'name="user1"')
    VALUES('person', 'name="user2"')
    VALUES('person', 'name="user3"')
    VALUES('person', 'name="user4"')
    ;
QUIT;
%rcSet(&sqlrc);

/* The following line will cause this program to ONLY extract the data (and not perform the load). */
/* %let _EXTRACTONLY = ; */

/* Extract identity information from the metadata (target). */
%mduextr(libref=META);
%rcSet(&syserr);

/* Compare AD (master) to metadata (target). */
%mducmp(master=XWALK, target=META, change=UPDATES, exceptions=EXCLUDE.exclusion_rules);
%rcSet(&syserr);

/* Validate the change tables. */
%mduchgv(change=UPDATES, target=META, temp=work, errorsds=work.mduchgverrors);
%rcSet(&syserr);

/* Load  the  changes into the metadata. */
%macro exec_mduchglb;
   %if (&MDUCHGV_ERRORS ^= 0) %then %do;
      %put ERROR: Validation errors detected by %nrstr(%mduchgv). Load not attempted.;
      %return;
      %end;
   %mduchglb(change=updates);
%mend;

%exec_mduchglb;
%rcSet(&syserr);

/*---- End of User Written Code  ----*/

View solution in original post


All Replies
Contributor
Posts: 73

Re: Create users/groups in SAS from SQL crosswalk table - sample script?

Oh...and this may complicate things.  I have a base set of users that I have defined (and manage) via Management Console (such as power users and admins).  I do NOT want these users/groups included in this synch/upload process.  The synch/upload will be for my "general" users and "general" groups (I will have MANY "general" users and groups).

 

Thanks,

Ricky

Super User
Posts: 5,424

Re: Create users/groups in SAS from SQL crosswalk table - sample script?

Canonical tables is the way to go. You should be able to update those with plain SQL.
Sample script - use the mdu family macros, documented in the platform security user guide I believe.
Data never sleeps
Trusted Advisor
Posts: 1,307

Re: Create users/groups in SAS from SQL crosswalk table - sample script?

Hello @rgreen33,

 

in short, if you manage to get your data into a CSV file, you can tranform this CSV into the cannonical tables thanks to the SAS Macro  %MDUIMPC, and then follow the general Bulk User loading process as docummented.

 

As @LinusH mentioned correctly, there is already some documentation at http://support.sas.com/documentation/cdl/en/bisecag/69827/HTML/default/viewer.htm#n0l2hp5m00a1z2n1b5... and it's related links in the same directory (see on the left side of the webpage).

 

In short, there are 2 main processes:

 

- one where you get the data (users and groups_ from an external source (AD, LDAP or CSV ( %MDUIMPC) ) and you convert this data into cannonical tables - SAS tables-  ( see the schema and requirements at http://support.sas.com/documentation/cdl/en/bisecag/69827/HTML/default/viewer.htm#p1o31lg0trorn8n1rk... ) which will serve to the next process.

 

- another, (%MDUEXTR) which will get all the current SAS Metadata users and groups into cannonical tables - SAS tables -, will query the SAS Metadata cannonical tables agains your cannonical tables and will make a "diff". This process (%MDUCMP ) is done with the Macros as described on the first link. This "diff" will distinguish between users and groups to be added, to be removed, and to be updated. This differentiation will be written into subsequent cannonical tables, on 3 different directories. 

 

Depending on your configuration and enabled flags, the macros (%MDUCHGV) are capable also to check integrity and to create a couple of additional tables for identifying problems before the update itself.

 

Example code of AD/LDAP: http://support.sas.com/documentation/cdl/en/bisecag/69827/HTML/default/viewer.htm#n16wd5khdis6qon1ac...

 

Example code for the user sync: http://support.sas.com/documentation/cdl/en/bisecag/69827/HTML/default/viewer.htm#p0z36im6qsfk3ln1ad...

Contributor
Posts: 73

Re: Create users/groups in SAS from SQL crosswalk table - sample script?

Posted in reply to JuanS_OCS

@JuanS_OCS, @LinusH,

 

Thank you both for your replies.  Looks like I am on the right track, as I already had these documents printed out and spread across my desk. :-)  I now have my data mapped to the necessary canonical table.  Now, I just need to get the SQL in place to actaully populate the tables.

 

Once I get this code in place, how do I make sure that my admin accounts and default (out of the box) groups do not get deleted?  As I mentioned, this "automated" piece should only be for my general users and groups.  Admin accounts and "out of the box" groups should not be touched.  Ideas?

 

Thanks,

Ricky

Trusted Advisor
Posts: 1,307

Re: Create users/groups in SAS from SQL crosswalk table - sample script?

Hi Ricky,

 

custom accounts should not be a problem, they will be kept.

 

SAS already has got some users and groups, and they are not deleted by the scripts.

 

The reason because they don't is because the scripts work with the keyID as primary key, which is the External Identity (if you go to a SAS account of group, on the first tab you will find a button for the External Identities). 

Contributor
Posts: 73

Re: Create users/groups in SAS from SQL crosswalk table - sample script?

Posted in reply to JuanS_OCS

@JuanS_OCS, @LinusH,

 

Again, thank you both for your replies.  I am getting close to my solution.  However, I have another question...just to make sure that I am on the right track.

 

So, I extract my info from my crosswalk table and poplulate the canonical tables of XWALK.  I then run %MDUEXTR to pull the data from metadat to load canonical tables of META.  Then, I run %MDUCMP to compare the two sets of data, to come up with the adds, updates, deletes (then I validate with %MDUCHGV).

 

Now, here's my question...using the libraries defined above.  When I look at META.person, I see that the keyid column contains values similar to the following:  A5V817F3.AP0000001.  Obvioualy, my XWALK.person does not have this value for the keyid column.  Thus, when I do the compare, it tells me that ALL users need to be added (even users that already exist).  Now, in my XWALK.person table, the name column is unique.  So, should I use the name column to query the META.person table and populate the XWALK.person keyid column?  Without this, I am not seeing how the compare can work.

 

Thanks,

Ricky

Contributor
Posts: 73

Re: Create users/groups in SAS from SQL crosswalk table - sample script?

Well, that did't work as planned.  I add the keyid from META.person to XWALK.person...and it is still telling me that the existing users still need to be added.  So, what am I missing here?  Why is the compare not catching my existing users?

 

Thanks,

Ricky

Trusted Advisor
Posts: 1,307

Re: Create users/groups in SAS from SQL crosswalk table - sample script?

[ Edited ]

Hello @rgreen33,

 

that is a good question, although it is not easy without looking carefully into the logs and all your user data (from the metadata export and crosswalk tables export)... which of course you  probably cannot/should not share here publicly, since it would contain a lot of sensible information.

 

Please let me recommend you to share this question with SAS Technical Support, I am sure they can take it from here.

 

Kind regards,


Juan

 

Solution
‎07-19-2017 11:39 AM
Contributor
Posts: 73

Re: Create users/groups in SAS from SQL crosswalk table - sample script?

Posted in reply to JuanS_OCS

First, let me thank @JuanS_OCS and @LinusH for your help.  With your help, I was able to create a solution that works for my setup.

 

So, here is my setup.  I work in Higher Education, so almost everything in my data maps to college codes and department codes.  However, I did not have a data sources that tied individuals to a college/department...and I needed this, as we plan to use SAS' row-level security.  In order to create this, I have several pieces...but, I wanted it all to work from Active Directory.  So, I created the following:

 

Active Directory:  I have an OU setup with 100 or so groups in it.  Each group contains any number of users.

SQL Server:  I have a job in SQL Server that is setup to run at the top of every hour.  This job essentailly gets my group membership for all group in my OU.  The last step of this job is to ETL the data to Oracle, which is where my crosswalk table exists.

Oracle:  In Oracle, I have my crosswalk table.  This crosswalk table maps each of my AD groups to colleges and departments.  A group can map to one or more colleges and one or more departments.  By have the crosswalk table at the group level, instead of having it at the user level, it is much easier to maintain.

SAS:  I have a job setup in SAS that joins the crosswalk table and the membership table (taking into account some speacial groups that gives members access to everything) and then pushes this data to Hadoop and then to LASR.  The result is a table that contains every user and every combination of college/department that he/she has access to.

 

Now, for my row-level security, as I mentioned, most (if not all) of my data contains columns for College Code and Department Code.  So, this is what I based by row-level security on.  By doing this, it means that I need a SAS Group for each College Code and each College Code || Department Code combination (as a department can belong to multiple colleges).

 

This is where my crosswalk table comes into play.  My crosswalk table contains exactly what I need...every College Code and College Code || Department Code combination.  I can base my SAS Groups off of this information...and my crosswalk table also contains the members for each group.  Perfect!  But, the problem was...how do I go from crosswalk table (which is a table that I have loaded into LASR) to SAS Groups and Users (by the way, my SAS enviroronment is setup to authenticate against Active Directory).  So, I started asking question in this post and started building a script.  I ended up with the below script setup as a Data Integration Studio job (with user written note).  This allowed me to then schedule this script after HDFS and LASR have both loaded, as I run this process hourly.

 

So, the results are this...I can now maintain all of my users and groups via AD.  I simply add a new user to an AD group, and the process with recognize that user and add him/her to SAS.  It will add my person record and add him/her to the appropriate groups.  The same thing goes for groups.  This process will also take care of my updates and deletes...which is exactly what I was looking for.  So, now for the script.  Enjoy.  I hope that this saves others some work.

 

A couple notes about the script:

  • You will notice that I have created a new MDUCHGV macro.  This is due to the following:  http://support.sas.com/kb/49/002.html
  • You will notice that in one of the proc sql steps below, I create a veiw.  This is due to the above...I was trying to get around that warning without having to modify the macro.  Since the macro has been modified, this could be cleaned up.
  • In my DI Studio job, I have two notes...User Written (which contains the code below) and Return Code Check (which checks the return code of the User Written node and emails me the results - Outlook rule files away the success emails, and flags any failures).
  • With my setup, I am only maintainging accounts/groups for my "general" users.  All admin accounts and power users are being handled manually (as these users do not appear in my crosswalk).  At some later time, I may modify the script to read info for my admins and power users directly from AD.  I am using my exclusions for these accounts...so that the script skips over them.

 

If anyone has any suggestions on how to improve this script, please post.  I am always looking for suggestions, as I am just getting stated in SAS.

 

_________________________________________________________

 

/*---- Start of User Written Code  ----*/

/* Use the edited version of the %MDUCHGV macro */
/* to prevent PROC SQL truncation warnings, per */
/* SAS Note 49002.                              */
%include '/data/sasdata/admin/mduchgv.sas';

/* Get METAServer from automatic macro variables */
%let var_METAServer = &METASERVER;

/*-------------------------------------------------------
* Define system options to connct to the metadata server.
*-------------------------------------------------------*/
options metaserver=your.sas.server.here 
        metaport=8561
        metauser="unrestricted_user_here@saspw"
        metapass="password_for_above_user__should_encode_this"
        metaprotocol=BRIDGE
        metarepository=Foundation;

/* Define the library where the extracted crosswalk info will be stored. */
libname XWALK "/data/sasdata/admin/XWALKextract";
%rcSet(&syslibrc);

/* Define the library where the extracted metadata info will be stored. */
libname META "/data/sasdata/admin/METAextract";
%rcSet(&syslibrc);

/* Define the library where the change data sets will be stored. */
libname UPDATES "/data/sasdata/admin/METAupdates";
%rcSet(&syslibrc);

/* Define the library where the Exclusions data sets will be stored. */
libname EXCLUDE "/data/sasdata/admin/Exclusions";
%rcSet(&syslibrc);

/* Clean up if last execute left tables in libraries. */
proc datasets library=XWALK memtype=data kill;
proc datasets library=META memtype=data kill;
proc datasets library=UPDATES memtype=data kill;
proc datasets library=EXCLUDE memtype=data kill;
run;
quit;

/* Create canonical tables for XWALK. */
%MDUIMPC (LIBREF=XWALK, MAKETABLE=1);
%rcSet(&syserr);

/* Define the library where the crosswalk info resides. */
LIBNAME LASRLIB SASIOLA  TAG=VAPUBLIC  PORT=10031 HOST="your_sas_host_name_here";
%rcSet(&syslibrc);

/* Populate XWALK.person. */
proc sql;
  INSERT INTO XWALK.person (keyid, name, displayname, description)
  SELECT DISTINCT NETID AS keyid, NETID AS name, NETID AS displayname, 'my (RLS) - Auto-Synched User' AS description
  FROM LASRLIB.my_SECURITY_CROSSWALK;
quit;
%rcSet(&sqlrc);

/* Populate XWALK.grpmems. */
proc sql;
  INSERT INTO XWALK.grpmems (grpkeyid, memkeyid)
  SELECT DISTINCT STRIP(LEFT(COLL_CODE))||STRIP(LEFT(COLL_DEPT)) AS grpkeyid, NETID AS memkeyid
  FROM LASRLIB.my_SECURITY_CROSSWALK
  WHERE COLL_CODE IS NOT NULL;
quit;
%rcSet(&sqlrc);

/* Populate XWALK.logins. */
proc sql;
  INSERT INTO XWALK.logins (keyid, userid, authdomkeyid)
  SELECT DISTINCT NETID AS keyid, NETID AS userid, 'DefaultAuth' as authdomkeyid
  FROM LASRLIB.my_SECURITY_CROSSWALK;
quit;
%rcSet(&sqlrc);

/* Populate XWALK.idgrps. */
proc sql;
  CREATE VIEW aview AS
  SELECT DISTINCT STRIP(LEFT(COLL_CODE))||STRIP(LEFT(COLL_DEPT)) AS keyid, SUBSTR((STRIP(LEFT(COLL_CODE))||STRIP(LEFT(COLL_DEPT))), 1, MIN(60, LENGTH(STRIP(LEFT(COLL_CODE))||STRIP(LEFT(COLL_DEPT))))) LENGTH=60  AS name,
  CASE
    WHEN COLL_DEPT IS NULL THEN SUBSTR((LEFT('my - (RLS) - ')||STRIP(TRIM(COLL_DESC))), 1, MIN(255, LENGTH(LEFT('my - (RLS) - ')||STRIP(TRIM(COLL_DESC)))))
    ELSE SUBSTR((LEFT('my - (RLS) - ')||STRIP(LEFT(COLL_DESC))||' - '||STRIP(LEFT(COLL_DEPT))), 1, MIN(255, LENGTH(LEFT('my - (RLS) - ')||STRIP(LEFT(COLL_DESC))||' - '||STRIP(LEFT(COLL_DEPT)))))
  END LENGTH=255 as displayname,
  CASE
    WHEN COLL_DEPT IS NULL THEN 'College'
    ELSE 'Department'
  END as description
  FROM LASRLIB.my_SECURITY_CROSSWALK
  WHERE COLL_CODE IS NOT NULL;
 
  INSERT INTO XWALK.idgrps (keyid, name, displayname, description)
  SELECT DISTINCT keyid, name, displayname, description
  FROM aview;
quit;
%rcSet(&sqlrc);

/* Populate XWALK.authdomain. */
proc sql;
  INSERT INTO XWALK.authdomain (keyid, authdomname)
  VALUES ('DefaultAuth', 'DefaultAuth');
quit;
%rcSet(&sqlrc);

/* Define exclusions for NETIDs - these are the NETIDs that have been added manually to SAS. */
/* This would account for power users and above - no need to worry about local SAS accounts. */
PROC SQL;
  CREATE TABLE EXCLUDE.exclusion_rules
    (  tablename CHAR(255),
       filter    CHAR(255)
    );
  INSERT INTO EXCLUDE.exclusion_rules
    VALUES('person', 'name="user1"')
    VALUES('person', 'name="user2"')
    VALUES('person', 'name="user3"')
    VALUES('person', 'name="user4"')
    ;
QUIT;
%rcSet(&sqlrc);

/* The following line will cause this program to ONLY extract the data (and not perform the load). */
/* %let _EXTRACTONLY = ; */

/* Extract identity information from the metadata (target). */
%mduextr(libref=META);
%rcSet(&syserr);

/* Compare AD (master) to metadata (target). */
%mducmp(master=XWALK, target=META, change=UPDATES, exceptions=EXCLUDE.exclusion_rules);
%rcSet(&syserr);

/* Validate the change tables. */
%mduchgv(change=UPDATES, target=META, temp=work, errorsds=work.mduchgverrors);
%rcSet(&syserr);

/* Load  the  changes into the metadata. */
%macro exec_mduchglb;
   %if (&MDUCHGV_ERRORS ^= 0) %then %do;
      %put ERROR: Validation errors detected by %nrstr(%mduchgv). Load not attempted.;
      %return;
      %end;
   %mduchglb(change=updates);
%mend;

%exec_mduchglb;
%rcSet(&syserr);

/*---- End of User Written Code  ----*/

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 354 views
  • 5 likes
  • 3 in conversation