Data Management Platform is a powerful tool for data cleansing and data integration applications. In Data Management Studio, you can use the Data Job Expression node to run a scripting language to process your data sets in ways that are not built by default. The Expression Engine Language (EEL) provides many statements, functions, and variables for manipulating data. Note: Expression Language Reference Guide is accessible from SAS Support website.   The Expression Engine Language can be used to code inside a Data Job, but can also be used to write your own functions that can then be saved outside of your jobs, and called from any of them.   For example, let's say you need to mesure a distance between strings and need to use a Jaro-Winckler distance.   First step is to create a text file inside your Data Management Studio etc/udf folder. This file will contain your code, and can be named anything you like. In each text file, you can write as many functions as you like, in that format:     function myFunctionName return returnType //function body end function In Data Management Studio, anything defined in a text file saved in /etc/udf folder as a function will appear under Expression Node Functions riser, in the Other group:     You can then call this function from any of your new Data Jobs:   If these functions are to be called from a job running on Data Management Server, udf files just need to be copied to the same location on the server side. ... View more

SAS Federation Server provides the business ability to quickly build virtual views of data from multiple sources, without moving or copying the data. It also supports data caching for frequently used data, which can result in improved query performance. The security features for SAS Federation Server include data masking, row-level security, and centralized access for improved governance.   SAS Scalable Performance Data Server is optimized to deliver subsets of information that need to be harvested from large stores of enterprise data, quickly and on demand.   Supported Versions   Starting with SAS Federation Server 4.2,  connectivity to SAS Scalable Performance Data Server 5.3 (SPDS) allowing READ, WRITE, and UPDATE to SPDS tables is possible. Before being able to use the driver to SPDS, you'll need to make sure you have installed SAS SPD Server 5.3 and all hotfixes (including SAS Federation Server, SAS Federation Server Manager, SAS Drivers for Federation Server, and SAS 9.4).   Configuring a Data Service in SAS Federation Server Manager   To access data that is hosted on SAS Federation Server, a client must submit a DSN that defines how to connect to the data. DSNs are associated with a data service that provides the foundation for the connection, such as user access control.        1.   Create a New Data Service and give it a unique name        2.   Use the Generic, without native catalog data service template        3.   Use the SPDS Driver Type Name and set the connection string: Connection string requires: HOST: the server name or address, SERV: the port, LOCALE, DBQ: the domain name, SCHEMA: a schema name associated with each DBQ you want to use. Note: it is required to have DBQ set at the "parent" portion of the connection string. Then, for each listed schema, a DBQ also needs to be set.        4.   Choose a Catalog name   Note: Catalog must be unique among all your Data Services.        5.   Select the authentication domain you want to use   This Authentication Domain needs to have been defined in your SAS Management Console. Your user - or a group he belongs to - needs to have this Authentication Domain set with a valid user/password in theu accounts.   Note: anonymous can't be used   6. Finish Once you click Finish, your Data Service will be available. If you expand it, you will see the Catalog name you defined and then a folder for each Schema you defined.   Your SPDS data can now be accessed and secured like any other data in Federation Server.   Required options if using a DDL statement   CATALOG='catalog-name': specifies the catalog name. DBQ='SPD-Server-domain-name': specifies the domain name. For SPD Server, a domain represents a specific directory of file storage locations. Data access is facilitated by using domains and a name server. DRIVER=SPDS: specifies the SAS Federation Server driver for SPD Server. HOST=host-name: specifies the SPD Server, or host name, to which you are connecting. SERV='port-number' (Alias: PORT): specifies the port number associated with the server, or host passed through the HOST option. LOCALE=locale: specifies the locale for the connection. USER=user-name (Alias: UID): specifies a user name that is registered in the server’s password database. PASSWORD=password (Alias: PWD, PASSWD). Required for the USER= option. Specifies a case-sensitive password that is associated with the user ID passed to the SPD Server. ... View more

Sometimes, your Data Management jobs will not run in the expected time, or you need to understand where the time is spent in your job. Performance Profiling exists for this.   How the log works   There are two modes of operation: Profile by node type – This will give you each node type (Cluster, Expression Matchcode, etc.) along with how many milliseconds were spent on each of three operations (prepare, pre-execute, execute), and how many times each was entered. Profile by node instance – This will give you each node instance and how many milliseconds were spent doing the same 3 operations for each, and how many times each was entered. The ID will correspond to the ID property of your job (as displayed in the node's Basic Settings), and will include the job name so you can see embedded jobs. The output will be comma delimited and the columns are as follows for type 1 above: Operation - Can be one of NX (cumulative time spent processing rows), PR (time spent preparing), PX (time spent pre-executing) Node type - The type of node Time - Time in milliseconds Entries (v 2.2 and later) - Number of times we entered that code Example output for 1 (action type, node type, milliseconds, entries): Profiler dump node types NX,CASE,49698,1000001 NX,CLUSTER,63895,910332 NX,DEST_DELIMTEXT,131,6871 NX,EXPRESSION,11860,917203 NX,MATCHCODE,6105360,1000001 NX,MATCHPARSED,530428,3000003 NX,SORT,10851,910332 NX,SOURCE_DELIMTEXT,30970,1000001 NX,STANDARDIZE,1332685,1000001 PR,CASE,0,1 PR,CLUSTER,0,1 PR,DEST_DELIMTEXT,0,1 PR,EXPRESSION,52,2 PR,MATCHCODE,3883,1 PR,MATCHPARSED,0,3 PR,SORT,0,1 PR,SOURCE_DELIMTEXT,27,1 PR,STANDARDIZE,0,1 PX,CASE,0,1 PX,CLUSTER,253,1 PX,DEST_DELIMTEXT,17,1 PX,EXPRESSION,10,2 PX,MATCHCODE,0,1 PX,MATCHPARSED,0,3 PX,SORT,1,1 PX,SOURCE_DELIMTEXT,0,1 PX,STANDARDIZE,20,1 For type 2: Operation - Can be one of NX (cumulative time spent processing rows), PR (time spent preparing), PX (time spent pre-executing) Filename - The job filename Node instance - This corresponds to the id of that node in the job (iid under "step" tag in job xml file) Time - Time in milliseconds Entries (v 2.2 and later) - Number of times we entered that code Example output for 2 (action type, job name, instance, milliseconds, entries): Profiler dump node instances NX,HDP_Duplicates_1.7_ServerTestProd27_1M.ddf,15,1331014,1000001 NX,HDP_Duplicates_1.7_ServerTestProd27_1M.ddf,17,207743,1000001 NX,HDP_Duplicates_1.7_ServerTestProd27_1M.ddf,18,155413,1000001 NX,HDP_Duplicates_1.7_ServerTestProd27_1M.ddf,19,171002,1000001 NX,HDP_Duplicates_1.7_ServerTestProd27_1M.ddf,33,131,6871 NX,HDP_Duplicates_1.7_ServerTestProd27_1M.ddf,34,10789,910332 NX,HDP_Duplicates_1.7_ServerTestProd27_1M.ddf,35,4103,6871 NX,HDP_Duplicates_1.7_ServerTestProd27_1M.ddf,36,8063,910332 NX,HDP_Duplicates_1.7_ServerTestProd27_1M.ddf,38,30698,1000001 NX,HDP_Duplicates_1.7_ServerTestProd27_1M.ddf,4,6102637,1000001 NX,HDP_Duplicates_1.7_ServerTestProd27_1M.ddf,5,62961,910332 NX,HDP_Duplicates_1.7_ServerTestProd27_1M.ddf,9,51321,1000001 PR,HDP_Duplicates_1.7_ServerTestProd27_1M.ddf,15,0,1 PR,HDP_Duplicates_1.7_ServerTestProd27_1M.ddf,17,0,1 PR,HDP_Duplicates_1.7_ServerTestProd27_1M.ddf,18,0,1 PR,HDP_Duplicates_1.7_ServerTestProd27_1M.ddf,19,0,1 PR,HDP_Duplicates_1.7_ServerTestProd27_1M.ddf,33,0,1 PR,HDP_Duplicates_1.7_ServerTestProd27_1M.ddf,34,0,1 PR,HDP_Duplicates_1.7_ServerTestProd27_1M.ddf,35,3,1 PR,HDP_Duplicates_1.7_ServerTestProd27_1M.ddf,36,48,1 PR,HDP_Duplicates_1.7_ServerTestProd27_1M.ddf,38,26,1 PR,HDP_Duplicates_1.7_ServerTestProd27_1M.ddf,4,3883,1 PR,HDP_Duplicates_1.7_ServerTestProd27_1M.ddf,5,0,1 PR,HDP_Duplicates_1.7_ServerTestProd27_1M.ddf,9,0,1 PX,HDP_Duplicates_1.7_ServerTestProd27_1M.ddf,15,20,1 PX,HDP_Duplicates_1.7_ServerTestProd27_1M.ddf,17,0,1 PX,HDP_Duplicates_1.7_ServerTestProd27_1M.ddf,18,0,1 PX,HDP_Duplicates_1.7_ServerTestProd27_1M.ddf,19,0,1 PX,HDP_Duplicates_1.7_ServerTestProd27_1M.ddf,33,17,1 PX,HDP_Duplicates_1.7_ServerTestProd27_1M.ddf,34,1,1 PX,HDP_Duplicates_1.7_ServerTestProd27_1M.ddf,35,10,1 PX,HDP_Duplicates_1.7_ServerTestProd27_1M.ddf,36,0,1 PX,HDP_Duplicates_1.7_ServerTestProd27_1M.ddf,38,0,1 PX,HDP_Duplicates_1.7_ServerTestProd27_1M.ddf,4,0,1 PX,HDP_Duplicates_1.7_ServerTestProd27_1M.ddf,5,252,1 PX,HDP_Duplicates_1.7_ServerTestProd27_1M.ddf,9,0,1   Enabling the log   To turn on the functionality, go into your app.cfg and add one or both of the following two lines, depending on which log you want to enable:   STEPENG/PROFILEBYNODE=true STEPENG/PROFILEBYTYPE=true     On Windows and versions superior to 2.4 only, also set the following:   BASE/HIGH_PRECISION_TIMER=1     If not set, the timings might be less accurate. However, if this IS set, all threads will run on CPU core 1 which will decrease the performance of the program if it's using multiple threads:   Then you’ll need to make sure you are seeing the log output. So in your log xml file, add something like this:   <appender class="RollingFileAppender" name="ProfFile"> <param name="Append" value="false"/> <param name="ImmediateFlush" value="true"/> <rollingPolicy class="TimeBasedRollingPolicy"> <param name="fileNamePattern" value="%S{OSENV.DIS_LOG_DIR}/profiler_service_%d_%S{pid}.log"/> </rollingPolicy> <layout> <param name="ConversionPattern" value="%m"/> </layout> </appender> <logger name="DF.RTProfiler"> <level value="Trace"/> <appender-ref ref="ProfFile"/> </logger>     The logs are pretty low volume so they shouldn’t affect performance or disk space.   Analyzing the log results   The output is comma delimited so you can pull it into excel. In order to analyse it, you can use Excel Pivot Table, which requires to have all fields splitted.   Input Operation Node Type Time Entries NX,CASE,49698,1000001 NX CASE 49698 1000001 NX,CLUSTER,63895,910332 NX CLUSTER 63895 910332 NX,DEST_DELIMTEXT,131,6871 NX DEST_DELIMTEXT 131 6871 NX,EXPRESSION,11860,917203 NX EXPRESSION 11860 917203 NX,MATCHCODE,6105360,1000001 NX MATCHCODE 6105360 1000001 NX,MATCHPARSED,530428,3000003 NX MATCHPARSED 530428 3000003 NX,SORT,10851,910332 NX SORT 10851 910332 NX,SOURCE_DELIMTEXT,30970,1000001 NX SOURCE_DELIMTEXT 30970 1000001 NX,STANDARDIZE,1332685,1000001 NX STANDARDIZE 1332685 1000001 PR,CASE,0,1 PR CASE 0 1 PR,CLUSTER,0,1 PR CLUSTER 0 1 PR,DEST_DELIMTEXT,0,1 PR DEST_DELIMTEXT 0 1 PR,EXPRESSION,52,2 PR EXPRESSION 52 2 PR,MATCHCODE,3883,1 PR MATCHCODE 3883 1 PR,MATCHPARSED,0,3 PR MATCHPARSED 0 3 PR,SORT,0,1 PR SORT 0 1 PR,SOURCE_DELIMTEXT,27,1 PR SOURCE_DELIMTEXT 27 1 PR,STANDARDIZE,0,1 PR STANDARDIZE 0 1 PX,CASE,0,1 PX CASE 0 1 PX,CLUSTER,253,1 PX CLUSTER 253 1 PX,DEST_DELIMTEXT,17,1 PX DEST_DELIMTEXT 17 1 PX,EXPRESSION,10,2 PX EXPRESSION 10 2 PX,MATCHCODE,0,1 PX MATCHCODE 0 1 PX,MATCHPARSED,0,3 PX MATCHPARSED 0 3 PX,SORT,1,1 PX SORT 1 1 PX,SOURCE_DELIMTEXT,0,1 PX SOURCE_DELIMTEXT 0 1 PX,STANDARDIZE,20,1 PX STANDARDIZE 20 1   You can then pull out information of this type:    Total Time Spent on DEV, PROD and difference, by node type       Percentage of Job's Time Spent on DEV, PROD, by Node Type ... View more

Customers often ask where they can get the SAS Data Management Repository database schema in order to query from it. We don't provide that schema, however in SAS Data Management Studio, you can access four nodes to get information from the repository.   1) Repository Info node   The Repository Info node can list all the repositories that are defined on the Administration riser in SAS Data Management Studio -- that's the only Repository node that can list information coming from several repositories.     It can also list other objects (rules, tasks, sources, dahboards) that are in the current repository, which is the repository that contains the data job that contains this node. You can use this node to identify specific rules or tasks that you want to reference in a data job.   The properties dialog includes a "Listing Method" field that will allow you to select what output you want: Repositories lists the repositories defined on the Administration riser, Dashboards lists the dashboards from the current repository, Execution Sources lists the sources from the current repository, Tasks lists the tasks from the current repository, Rules lists the rules from the current repository. This node can also return Tasks filtered by Name or Id, and Rules filtered by Name or Id, or Task Name or Id. When you select one of the Listing Method that requires a filter, you can either chose to filter the data using a field, or a value: use "value" if you already know the RULE_ID or TASK_ID you want to filter on; use "field" if your node has a parent that contains your filtered values.     2) Repository Primary node   The Repository Primary node lists summary information about the executions of all tasks in the current repository. The information for each execution includes the date and time of the execution, the task ID, the business rule ID, and other information that could be used as a reference in other nodes in a data monitoring job, such as number of triggers, status, dimension, importance, etc.     3) Repository Detail node   The Repository Detail node lists detailed information about the executions of all tasks in the current repository that have had triggers. It needs an EXEC_ID (Execution) in input, either from a field, or from a manual value you enter. It will then return the information for each execution, including values for each row returned by the task. You can then filter on a Rule Id too, using a value, or a field.   This node is often used in combination with other monitoring nodes. For example, you could create a data job where output from the Repository Primary node was sent to another node that selected the latest execution ID for a particular task. This execution ID could be sent to the Repository Detail node, which would list detailed information about that execution.     4) Repository Log node   The Repository Log node lists fields that are associated with the business rules that are defined in the current repository. This node is used by some solutions. It needs a Task Id and Rule Id, either coming from a parent node, or manually entered.       ... View more

Sometimes, you get data in a format where you need to pivot from column to row. For example, your customers have several phone numers, kept as MOBILE, OFFICE, HOME, etc. on the same record and you'd like to pivot that table.     Such functionality doesn't exist in SAS Data Management Studio, but can be achieved by some simple Expression code. In the Pre-Processing Expression code (before I start to read my input records), I will need to declare my variables first and set some values:   //New array that will contain my field list hidden string array fieldlist //New string that will contain indexes for fields I want to pivot hidden string indexlist //New integer for the number of fields that I want to pivot hidden integer dim //Loop integers hidden integer i hidden integer j //New fields created string PhoneTypestring PhoneNumber //Indexes for the fields I want to pivot indexlist = '2;3;4' //Number of fields to pivot dim = aparse(indexlist,';',fieldlist)   In the Expression code (what will be executed for each record), I will loop on all existing fields, then set the value for my new columns:   //Loop on all fields for i = 1 to fieldcount() Begin for j = 1 to dim Begin if fieldname(i) == fieldname(fieldlist.get(j)) then Begin PhoneType = fieldname(i) PhoneNumber = fieldvalue(i) pushrow() End End End return false   Notes: The fieldname(integer) function returns a string, representing the name of a specific field output from the parent node. The fieldvalue(integer) function returns a string, representing the value of a specific field output from the parent node. The <array_name>.get(integer) function retrieves the value of the specified item within an array. The returned value is in the type of the array. The pushrow function pushes the current values of all symbols (this includes both field values for the current row and defined symbols in the code) to a stack. It returns a boolean (true = success ; false = error).   At this point, I have duplicated my Phone Numbers into 2 new columns, and I just need to use a Field Layout node to remove the unwanted columns.      A Data Job build with Data Management Studio 2.6 is attached to this article, that contains the code to pivot data. ... View more

In data and process jobs, you can read and write to SAS Data Sets. But before you do that, you need to create a connection to these SAS Datasets. Here's how to do it in SAS Data Management Studio and Server.   Data Management Studio   In the Data Management Studio home page, go to the Data riser, right click on Data Connections > New Data Connection and then select SAS Data Set Connection.   A new SAS Data Connection dialog opens:     Enter a name (mandatory) and description. Enter the path to the directory (mandatory) that contains one or more SAS data sets. You do not have to create one connection for each file, but one connection for each folder. It has to be a path you can access from that machine. If the SAS Datasets are not located on that machine, you need to have a valid path to access them. Verify that the appropriate option is specified in the Access field. DEFAULT is read-write access. READ-ONLY assigns a read-only access. TEMP specifies that the data set be treated as a scratch file. Verify that the appropriate option is specified in the Compression field. If you compress data, you might experience a slowdown in performance. NO specifies that the observations are uncompressed. Use YES or CHAR to compress character data. Use BINARY to compress binary data. Verify that the appropriate option is specified in the Table Locking field. SHARE specifies that other users or processes can read data from the table but prevents other users from updating. EXCLUSIVE locks tables exclusively which prevents other users from accessing any table you open. Verify that the appropriate option is specified in the Encoding field. The default is the SAS System encoding. You might select an encoding that is different than the default if you are processing a file with a different encoding. You can select an encoding from the drop-down menu. If you type the first few letters of the desired encoding, the closest match will be added to this field. A list of available encodings is available in Data Management Studio HTML help under Appendixes > Encodings for SAS Data Sets. Connection String - Displays the connection string for the SAS data set. Check the connection string to see if the appropriate encodings option has been selected for this connection. You can test the connection by clicking Test Connection. Then, click OK to save the new connection and return to the Data riser. To see the new connection in the Data Connections folder, you'll need to Refresh the Data Riser.   By default, it will create a new dftk file under Data Management Studio etc\dftkdsn folder. This dftk file name will be the name of your Data Connection. You can open that file in a text editor, which will look like this:   <?xml version="1.0" encoding="UTF-8" standalone="no" ?> <datafluxdocument class="dftkdsn" version="1.0"> <name>My Data Connection</name> <description>Its Description</description> <extprops>DFTYPE=SAS;</extprops> <type>TKTS</type> <domain></domain> <attributes> <attribute name="CATALOG">BASE</attribute> <attribute name="DRIVER">BASE</attribute> <attribute name="Schema">(name='SAS';primarypath='Path\To\Data\';COMPRESS=NO;LOCKTABLE=SHARE;)</attribute> </attributes> </datafluxdocument> Administrators can specify where SAS data set connections are stored using the TKTS DSN directory setting.   Data Management Server   In Data Management Server, there is no dialog to create the connection. The easiest way is to create one is in Data Management Studio, and then copy the dftk file from Data Management Studio etc\dftkdsn to the Data Management Server dftkdsn folder. If the Data Management Server is located on a different machine, you'll most likely need to edit the primarypath value. Also, you need to verify that the compression, sharing and encoding values are still correct.   ... View more