Amadeus cookies policy - you'll see this message only once.

Amadeus use cookies on this website. They help us to know a little bit about you and how you use our website, which improves the browsing experience and marketing - both for you and for others. They are stored locally on your computer or mobile device. To accept cookies, continue browsing as normal. Or, go to the privacy policy for more information.

Using Proc FCMP to Create User Defined Functions for Use in SAS® Visual Analytics

Proc FCMP has been available to DATA step programmers since SAS version 9.2 and is recognised as a very valuable procedure for generating user defined functions. Out of the box, SAS has over 400 functions available. Proc FCMP allows you to extend these and write your own functions. These can be used to solve complex coding problems and be shared around the organisation for common use.

This tip explains how a user defined function, generated with Proc FCMP, can be used within SAS® Visual Analytics. An example is demonstrated, using the user defined function to generate a data field within a data query, via the Visual Data Builder.

Note, before attempting this, check that Proc FCMP is licensed at your site. For installations with only SAS Visual Analytics installed, this may not be the case.

This example was generated using SAS Visual Analytics version 7.3.

Create the Function

Create the function on your server using SAS Display Manager, SAS Enterprise Guide® software or SAS Studio. Initially set a location on the server to store the compiled function, and apply a library reference to this:

Using Proc FMCP Amadeus SAS Tip Image 1

Then use Proc FCMP to create your function in this location. The example used here creates a function to calculate the number of working days between two dates. It is equivalent to the Excel function ‘networkdays’. Further details for this function can be found here.

Using Proc FMCP Amadeus SAS Tip Image 2

Submitting this code creates a dataset fcmp.myfuncs which defines the function. The function is called ‘networkdays’. This particular function also requires a lookup dataset, to define non-working days. Function arguments are:

  • Start Date
  • End Date
  • Non-working days dataset
  • Date field within the non-working days dataset. 

Example code to define the non-working days is given below:

Using Proc FMCP Amadeus SAS Tip Image 3

To test the function within your environment, first of all set a CMPLIB options statement to point to the compiled function and then run some test code using the networkdays function:

Using Proc FMCP Amadeus SAS Tip Image 4

Using the Function within a SAS Visual Analytics Query

Within SAS Visual Analytics, generate a query or modify an existing query, using the Visual Data Builder, via the ‘Prepare Data’ drop down option from the Home page.

First of all ensure that SAS can see the defined function, by setting the following libref and options statements within the Preprocess code:

Using Proc FMCP Amadeus SAS Tip Image 5

The function can then be used, just like a pre-defined function. For example to set a variable ‘Working_Days’, by using the networkdays function in the Expression column.

Using Proc FMCP Amadeus SAS Tip Image 6

The code generated by the Visual Data Builder, now includes a Proc SQL step that uses the networkdays function:

Using Proc FMCP Amadeus SAS Tip Image 7

Saving and running the query produces the following example output table:

Using Proc FMCP Amadeus SAS Tip Image 8

The field is now available for use within the Report Builder:

Using Proc FMCP Amadeus SAS Tip Image 9