When you fit nonlinear fixed-effect or mixed models, it is difficult to guess the model parameters that fit the data. Yet, most nonlinear regression procedures (such as PROC NLIN and PROC NLMIXED in SAS) require that you provide a good guess! If your guess is not good, the fitting algorithm, which is often a nonlinear optimization algorithm, might not converge. This seems to be a Catch-22 paradox: You can't estimate the parameters until you fit the model, but you can't fit the model until you provide an initial guess!

Fortunately, SAS provides a way to circumvent this paradox: you can specify multiple initial parameter values for the nonlinear regression models in PROC NLIN and PROC NLMIXED. The procedure will evaluate the model on a grid that results from all combinations of the specified values and initialize the optimization by using the value in the grid that provides the best fit. This article shows how to specify a grid of initial values for a nonlinear regression model.

### An example that specifies a grid of initial values

The following data and model is from the documentation of the NLMIXED procedure. The data describe the number of failures and operating hours for 10 pumps. The pumps are divided into two groups: those that are operated continuously (group=1) and those operated intermittently (group=2). The number of failures is modeled by a Poisson distribution whose parameter (lambda) is fit by a linear regression with separate slopes and intercepts for each group. The example in the documentation specifies a single initial value (0 or 1) for each of the five parameters. In contrast, the following call to PROC NLMIXED specifies multiple initial values for each parameter:

```data pump; input y t group;q pump = _n_; logtstd = log(t) - 2.4564900; datalines; 5 94.320 1 1 15.720 2 5 62.880 1 14 125.760 1 3 5.240 2 19 31.440 1 1 1.048 2 1 1.048 2 4 2.096 2 22 10.480 2 ;   proc nlmixed data=pump; parms logsig 0 1 /* multiple initial guesses for each parameter */ beta1 -1 to 1 by 0.5 beta2 -1 to 1 by 0.5 alpha1 1 5 10 alpha2 1 5; /* use BEST=10 option to see top 10 choices */ if (group = 1) then eta = alpha1 + beta1*logtstd + e; else eta = alpha2 + beta2*logtstd + e; lambda = exp(eta); model y ~ poisson(lambda); random e ~ normal(0,exp(2*logsig)) subject=pump; run;```

On the PARMS statement, several guesses are provided for each parameter. Two or three guesses are listed for the logsig, alpha1, and alpha2 parameters. An arithmetic sequence of values is provided for the beta1 and beta2 parameters. The syntax for an arithmetic sequence is start TO stop BY increment, although you can omit the BY clause if the increment is 1. In this example, the arithmetic sequence is equivalent to the list -1, -0.5, 0, 0.5, 1.

The procedure will form the Cartesian product of the guesses for each parameter. Thus the previous syntax specifies a uniform grid of points in parameter space that contains 2 x 5 x 5 x 3 x 2 = 300 initial guesses. As this example indicates, the grid size grows geometrically with the number of values for each parameter. Therefore, resist the urge to use a fine grid for each parameter. That will result in many unnecessary evaluations of the log-likelihood function.

The following table shows a few of the 300 initial parameter values, along with the negative log-likelihood function evaluated at each set of parameters. From among the 300 initial guesses, the procedure will find the combination that results in the smallest value of the negative log-likelihood function and use that value to initialize the optimization algorithm.

If you want to visualize the range of values for the log-likelihood function, you can graph the negative log-likelihood versus the row number. In the following graph, a star indicates the parameter value in the grid that has the smallest value of the negative log-likelihood. I used the IMAGEMAP option on the ODS GRAPHICS statement to turn on tool tips for the graph, so that the parameter values for each point appears when you hover the mouse pointer over the point.

The graph shows that about 10 parameter values have roughly equivalent log-likelihood values. If you want to see those values in a table, you can add the BEST=10 option on the PARMS statement. In this example, there is little reason to choose the smallest parameter value over some of the other values that have a similar log-likelihood.

### Create a file for the initial parameters

The ability to specify lists and arithmetic sequences is powerful, but sometimes you might want to use parameter values from a previous experiment or study or from a simplified model for the data. Alternatively, you might want to generate initial parameter values randomly from a distribution such as the uniform, normal, or exponential distributions. The PARMS statement in PROC NLMIXED supports a DATA= option that enables you to specify a data set for which each row is a set of parameter values. (In PROC NLIN and other SAS procedures, use the PDATA= option.) For example, the following DATA step specifies two or three values for the logsig, alpha1, and alpha2 parameters. It specifies random values in the interval (-1, 1) for the beta1 and beta2 parameters. The ParamData data set has 12 rows. The NLMIXED procedure evaluates the model on these 12 guesses, prints the top 5, and then uses the best value to initialize the optimization algorithm:

```data ParamData; call streaminit(54321); do logsig = 0, 1; do alpha1 = 1, 5, 10; do alpha2 = 1, 5; beta1 = -1 + 2*rand("uniform"); /* beta1 ~ U(-1, 1) */ beta2 = -1 + 2*rand("uniform"); /* beta2 ~ U(-1, 1) */ output; end; end; end; run;   proc nlmixed data=pump; parms / DATA=ParamData BEST=5; /* read guesses for parameters from data set */ if (group = 1) then eta = alpha1 + beta1*logtstd + e; else eta = alpha2 + beta2*logtstd + e; lambda = exp(eta); model y ~ poisson(lambda); random e ~ normal(0,exp(2*logsig)) subject=pump; run;```

As mentioned, the values for the parameters can come from anywhere. Sometimes random values are more effective than values on a regular grid. If you use a BOUNDS statement to specify valid values for the parameters, any infeasible parameters are discarded and only feasible parameter values are used.

### Summary

In summary, this article provides an example of a syntax to specify a grid of initial parameters. SAS procedures that support a grid search include NLIN, NLMIXED, MIXED and GLIMMIX (for covariance parameters), SPP, and MODEL. You can also put multiple guesses into a "wide form" data set: the names of the parameters are the columns and each row contains a different combination of the parameters.

It is worth emphasizing that if your model does not converge, it might not be the parameter search that is at fault. It is more likely that the model does not fit your data. Although the technique in this article is useful for finding parameters so that the optimization algorithm converges, no technique cannot compensate for an incorrect model.

The post Use a grid search to find initial parameter values for regression models in SAS appeared first on The DO Loop.

When you fit nonlinear fixed-effect or mixed models, it is difficult to guess the model parameters that fit the data. Yet, most nonlinear regression procedures (such as PROC NLIN and PROC NLMIXED in SAS) require that you provide a good guess! If your guess is not good, the fitting algorithm, which is often a nonlinear optimization algorithm, might not converge. This seems to be a Catch-22 paradox: You can't estimate the parameters until you fit the model, but you can't fit the model until you provide an initial guess!

Fortunately, SAS provides a way to circumvent this paradox: you can specify multiple initial parameter values for the nonlinear regression models in PROC NLIN and PROC NLMIXED. The procedure will evaluate the model on a grid that results from all combinations of the specified values and initialize the optimization by using the value in the grid that provides the best fit. This article shows how to specify a grid of initial values for a nonlinear regression model.

### An example that specifies a grid of initial values

The following data and model is from the documentation of the NLMIXED procedure. The data describe the number of failures and operating hours for 10 pumps. The pumps are divided into two groups: those that are operated continuously (group=1) and those operated intermittently (group=2). The number of failures is modeled by a Poisson distribution whose parameter (lambda) is fit by a linear regression with separate slopes and intercepts for each group. The example in the documentation specifies a single initial value (0 or 1) for each of the five parameters. In contrast, the following call to PROC NLMIXED specifies multiple initial values for each parameter:

```data pump; input y t group;q pump = _n_; logtstd = log(t) - 2.4564900; datalines; 5 94.320 1 1 15.720 2 5 62.880 1 14 125.760 1 3 5.240 2 19 31.440 1 1 1.048 2 1 1.048 2 4 2.096 2 22 10.480 2 ;   proc nlmixed data=pump; parms logsig 0 1 /* multiple initial guesses for each parameter */ beta1 -1 to 1 by 0.5 beta2 -1 to 1 by 0.5 alpha1 1 5 10 alpha2 1 5; /* use BEST=10 option to see top 10 choices */ if (group = 1) then eta = alpha1 + beta1*logtstd + e; else eta = alpha2 + beta2*logtstd + e; lambda = exp(eta); model y ~ poisson(lambda); random e ~ normal(0,exp(2*logsig)) subject=pump; run;```

On the PARMS statement, several guesses are provided for each parameter. Two or three guesses are listed for the logsig, alpha1, and alpha2 parameters. An arithmetic sequence of values is provided for the beta1 and beta2 parameters. The syntax for an arithmetic sequence is start TO stop BY increment, although you can omit the BY clause if the increment is 1. In this example, the arithmetic sequence is equivalent to the list -1, -0.5, 0, 0.5, 1.

The procedure will form the Cartesian product of the guesses for each parameter. Thus the previous syntax specifies a uniform grid of points in parameter space that contains 2 x 5 x 5 x 3 x 2 = 300 initial guesses. As this example indicates, the grid size grows geometrically with the number of values for each parameter. Therefore, resist the urge to use a fine grid for each parameter. That will result in many unnecessary evaluations of the log-likelihood function.

The following table shows a few of the 300 initial parameter values, along with the negative log-likelihood function evaluated at each set of parameters. From among the 300 initial guesses, the procedure will find the combination that results in the smallest value of the negative log-likelihood function and use that value to initialize the optimization algorithm.

If you want to visualize the range of values for the log-likelihood function, you can graph the negative log-likelihood versus the row number. In the following graph, a star indicates the parameter value in the grid that has the smallest value of the negative log-likelihood. I used the IMAGEMAP option on the ODS GRAPHICS statement to turn on tool tips for the graph, so that the parameter values for each point appears when you hover the mouse pointer over the point.

The graph shows that about 10 parameter values have roughly equivalent log-likelihood values. If you want to see those values in a table, you can add the BEST=10 option on the PARMS statement. In this example, there is little reason to choose the smallest parameter value over some of the other values that have a similar log-likelihood.

### Create a file for the initial parameters

The ability to specify lists and arithmetic sequences is powerful, but sometimes you might want to use parameter values from a previous experiment or study or from a simplified model for the data. Alternatively, you might want to generate initial parameter values randomly from a distribution such as the uniform, normal, or exponential distributions. The PARMS statement in PROC NLMIXED supports a DATA= option that enables you to specify a data set for which each row is a set of parameter values. (In PROC NLIN and other SAS procedures, use the PDATA= option.) For example, the following DATA step specifies two or three values for the logsig, alpha1, and alpha2 parameters. It specifies random values in the interval (-1, 1) for the beta1 and beta2 parameters. The ParamData data set has 12 rows. The NLMIXED procedure evaluates the model on these 12 guesses, prints the top 5, and then uses the best value to initialize the optimization algorithm:

```data ParamData; call streaminit(54321); do logsig = 0, 1; do alpha1 = 1, 5, 10; do alpha2 = 1, 5; beta1 = -1 + 2*rand("uniform"); /* beta1 ~ U(-1, 1) */ beta2 = -1 + 2*rand("uniform"); /* beta2 ~ U(-1, 1) */ output; end; end; end; run;   proc nlmixed data=pump; parms / DATA=ParamData BEST=5; /* read guesses for parameters from data set */ if (group = 1) then eta = alpha1 + beta1*logtstd + e; else eta = alpha2 + beta2*logtstd + e; lambda = exp(eta); model y ~ poisson(lambda); random e ~ normal(0,exp(2*logsig)) subject=pump; run;```

As mentioned, the values for the parameters can come from anywhere. Sometimes random values are more effective than values on a regular grid. If you use a BOUNDS statement to specify valid values for the parameters, any infeasible parameters are discarded and only feasible parameter values are used.

### Summary

In summary, this article provides an example of a syntax to specify a grid of initial parameters. SAS procedures that support a grid search include NLIN, NLMIXED, MIXED and GLIMMIX (for covariance parameters), SPP, and MODEL. You can also put multiple guesses into a "wide form" data set: the names of the parameters are the columns and each row contains a different combination of the parameters.

It is worth emphasizing that if your model does not converge, it might not be the parameter search that is at fault. It is more likely that the model does not fit your data. Although the technique in this article is useful for finding parameters so that the optimization algorithm converges, no technique cannot compensate for an incorrect model.

The post Use a grid search to find initial parameter values for regression models in SAS appeared first on The DO Loop.

### Once upon a Time

Once upon a time, Oliver S. Füßling merely occupied a line in a SAS® program. But one day, he lost his last name, and a quest began to help our hero find the rest of his name.

### Our Story Begins

The SAS Training Center wanted to re-create course data for the "Introduction to Programming 1" class. The updated class uses SAS® Studio, a new programming environment that incorporates a UTF-8 SAS session encoding. However, the course data sets contained national language characters, which are not available on an English keyboard. As a result, depending on how those programs were submitted in the new environment, they experienced the following transcoding problems:

• character substitution
• data truncation
• invalid-data errors

Like the Training Center, you might encounter similar transcoding issues if you have programs that:

• contain national language characters
• are created in the WLatin-1 SAS session encoding
• you move to a UTF-8 SAS session encoding.

This story explains how you can move such programs successfully to a UTF-8 environment and avoid substitution characters, data truncation, and invalid-data errors.

The programs in the "Introduction to Programming 1" class were originally submitted via an earlier English edition of the SAS® Foundation. However, the sample program in this story is created in SAS® 9.4 (English).

When the program is opened in the Enhanced Editor window, this is how a shortened version of the program looks:

Note: If you would like a copy of this program for your own testing, see the Epilogue heading at the end of this post.

In SAS 9.4 (English) for the Windows environment, the default session encoding is WLatin-1. You can see the encoding in the log by running either of the following sets of statements:

• PROC OPTIONS OPTION=ENCODING;
RUN;
• %PUT ENCODING= %SYSFUNC(getOption(ENCODING));

When programs are saved from the Enhanced Editor window, the encoding for the program file defaults to Default - Western (Windows), as shown below.

When the program file that is shown earlier, which contains the name Oliver S. Füßling, is uploaded and included into the SAS Studio code editor, Oliver's last name displays replacement characters rather than the expected national language characters.

Note: This display shows SAS Studio open in a Google Chrome browser. In this browser, you see two characters (diamonds with white question marks) that are substituted for the national language characters.  If you use SAS Studio in Microsoft Internet Explorer, the display shows only one diamond, and it truncates the remainder of the name.

To begin resolving the display problem, you need to look at the code-editor status bar (bottom of the window).

Notice that there is a text-encoding setting that informs SAS Studio of the encoding of the external file. That setting is shown to the right on the status bar. In the display above, that encoding is UTF-8.

Be aware that this text-encoding setting differs from the UTF-8 SAS session encoding that is displayed by the SAS ENCODING system option, which is generated in the log when you run the OPTIONS procedure. In SAS Studio, the default text encoding is UTF-8, regardless of the session encoding. Because the pgm.sas program was saved originally from the Enhanced Editor in the default Western-Windows encoding, it is not in the encoding that the SAS Studio code editor expects.

To fix the display issue, you can use either of the following options:

Option 1

1.  Right-click the program file and select Open with text encoding.

2.  In the Select Text Encoding dialog box, select the windows-1252 encoding value from the Navigation Pane menu.

The Windows code page 1252 represents the character set that is used by Western European languages, including English, in Microsoft Windows operating environments. The WLatin-1 encoding is the SAS equivalent for the 1252 Windows code page.[1]

3.  Click OK to save your selection before you exit the dialog box.

Option 2

From the General tab in the Preferences dialog box, select a value for the default text encoding.

When you set the value in this way, the change is not reflected immediately in the existing code- editor window. You must close the program and re-open it for the setting to take effect. Any programs that you open later will retain the same setting unless you change the setting or override it by selecting another value in the Select Text Encoding dialog box.

Oliver's last name is displayed correctly in the code editor when you use the windows-1252 setting to open the file, as shown below:

However, Oliver's last name is truncated on the HTML Results tab when you submit the program.

### The Plot Thickens

Although the problem is fixed in the code editor when you submit the program, Oliver's last name is truncated as Füßli in the output. However, you do not receive any notes or warnings in the log about that truncation. So, why does the truncation happen?  The ü (U-umlaut) and the ß (German Eszett) are stored as single-byte characters (SBCS) in WLatin-1, but those characters require two bytes in UTF-8. As a result, there is not adequate space to print the remaining characters in the name.

When you submit programs that contain national language characters from a single-byte encoding to a UTF-8 environment, you must be prepared to modify the program to use wider informats when you create your variables. Otherwise, character truncation can occur.

You can correct this problem easily by enlarging the column to accommodate the extra bytes that are used to store the characters in UTF-8.

Here is the modified INPUT statement that successfully reads the data in a UTF-8 SAS session. The character informat for the Lname variable is increased from \$7. to \$9.

`input StudID \$12. Age Fname \$6. Mi :\$2. Lname \$9.;`

After you increase the informat, Oliver's last name is correct when you view it on the HTML Results tab.

### A Subplot Appears

What if the program file is included and executed by using the %INCLUDE statement rather than by submitting it from the code editor?

In this situation, the program stops processing with the following errors:

```NOTE: The data set WORK.TEST has 1 observations and 5 variables. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds   ERROR: Invalid characters were present in the data. ERROR: An error occurred while processing text data. NOTE: The SAS System stopped processing this step because of errors.   NOTE: There were 1 observations read from the data set WORK.TEST.```

In this case, the HTML Results tab does not display a last name at all.

To eliminate this error, you need to use the ENCODING= option in the %INCLUDE statement, as shown below.

`%include "your-directory/pgm.sas" /encoding="windows-1252";`

By including the ENCODING="windows-1252" option in the %INCLUDE statement, the program now executes successfully, as shown by the notes in the log:

``` NOTE: The data set WORK.TEST has 1 observations and 5 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.01 seconds     NOTE: There were 1 observations read from the data set WORK.TEST.```

### Happily Ever After (or, The End)!

The moral of this story is that there are many ways to avoid transcoding problems when you have national language characters in SAS programs that you save from a SAS®9 (English) session and move to a UTF-8 environment. Hopefully, you can use the tips that are provided to avoid such issues. However, if you still have problems, you can call on another hero, SAS Technical Support, for help!

### Epilogue

The following program is the one used throughout this story. You can copy and paste it for your own use.

```data test; input StudID \$12. Age Fname \$6. Mi :\$2. Lname \$7.; datalines; 120400310496 15 Oliver S. Füβling ;   proc print; run;```

### Once upon a Time

Once upon a time, Oliver S. Füßling merely occupied a line in a SAS® program. But one day, he lost his last name, and a quest began to help our hero find the rest of his name.

### Our Story Begins

The SAS Training Center wanted to re-create course data for the "Introduction to Programming 1" class. The updated class uses SAS® Studio, a new programming environment that incorporates a UTF-8 SAS session encoding. However, the course data sets contained national language characters, which are not available on an English keyboard. As a result, depending on how those programs were submitted in the new environment, they experienced the following transcoding problems:

• character substitution
• data truncation
• invalid-data errors

Like the Training Center, you might encounter similar transcoding issues if you have programs that:

• contain national language characters
• are created in the WLatin-1 SAS session encoding
• you move to a UTF-8 SAS session encoding.

This story explains how you can move such programs successfully to a UTF-8 environment and avoid substitution characters, data truncation, and invalid-data errors.

The programs in the "Introduction to Programming 1" class were originally submitted via an earlier English edition of the SAS® Foundation. However, the sample program in this story is created in SAS® 9.4 (English).

When the program is opened in the Enhanced Editor window, this is how a shortened version of the program looks:

Note: If you would like a copy of this program for your own testing, see the Epilogue heading at the end of this post.

In SAS 9.4 (English) for the Windows environment, the default session encoding is WLatin-1. You can see the encoding in the log by running either of the following sets of statements:

• PROC OPTIONS OPTION=ENCODING;
RUN;
• %PUT ENCODING= %SYSFUNC(getOption(ENCODING));

When programs are saved from the Enhanced Editor window, the encoding for the program file defaults to Default - Western (Windows), as shown below.

When the program file that is shown earlier, which contains the name Oliver S. Füßling, is uploaded and included into the SAS Studio code editor, Oliver's last name displays replacement characters rather than the expected national language characters.

Note: This display shows SAS Studio open in a Google Chrome browser. In this browser, you see two characters (diamonds with white question marks) that are substituted for the national language characters.  If you use SAS Studio in Microsoft Internet Explorer, the display shows only one diamond, and it truncates the remainder of the name.

To begin resolving the display problem, you need to look at the code-editor status bar (bottom of the window).

Notice that there is a text-encoding setting that informs SAS Studio of the encoding of the external file. That setting is shown to the right on the status bar. In the display above, that encoding is UTF-8.

Be aware that this text-encoding setting differs from the UTF-8 SAS session encoding that is displayed by the SAS ENCODING system option, which is generated in the log when you run the OPTIONS procedure. In SAS Studio, the default text encoding is UTF-8, regardless of the session encoding. Because the pgm.sas program was saved originally from the Enhanced Editor in the default Western-Windows encoding, it is not in the encoding that the SAS Studio code editor expects.

To fix the display issue, you can use either of the following options:

Option 1

1.  Right-click the program file and select Open with text encoding.

2.  In the Select Text Encoding dialog box, select the windows-1252 encoding value from the Navigation Pane menu.

The Windows code page 1252 represents the character set that is used by Western European languages, including English, in Microsoft Windows operating environments. The WLatin-1 encoding is the SAS equivalent for the 1252 Windows code page.[1]

3.  Click OK to save your selection before you exit the dialog box.

Option 2

From the General tab in the Preferences dialog box, select a value for the default text encoding.

When you set the value in this way, the change is not reflected immediately in the existing code- editor window. You must close the program and re-open it for the setting to take effect. Any programs that you open later will retain the same setting unless you change the setting or override it by selecting another value in the Select Text Encoding dialog box.

Oliver's last name is displayed correctly in the code editor when you use the windows-1252 setting to open the file, as shown below:

However, Oliver's last name is truncated on the HTML Results tab when you submit the program.

### The Plot Thickens

Although the problem is fixed in the code editor when you submit the program, Oliver's last name is truncated as Füßli in the output. However, you do not receive any notes or warnings in the log about that truncation. So, why does the truncation happen?  The ü (U-umlaut) and the ß (German Eszett) are stored as single-byte characters (SBCS) in WLatin-1, but those characters require two bytes in UTF-8. As a result, there is not adequate space to print the remaining characters in the name.

When you submit programs that contain national language characters from a single-byte encoding to a UTF-8 environment, you must be prepared to modify the program to use wider informats when you create your variables. Otherwise, character truncation can occur.

You can correct this problem easily by enlarging the column to accommodate the extra bytes that are used to store the characters in UTF-8.

Here is the modified INPUT statement that successfully reads the data in a UTF-8 SAS session. The character informat for the Lname variable is increased from \$7. to \$9.

`input StudID \$12. Age Fname \$6. Mi :\$2. Lname \$9.;`

After you increase the informat, Oliver's last name is correct when you view it on the HTML Results tab.

### A Subplot Appears

What if the program file is included and executed by using the %INCLUDE statement rather than by submitting it from the code editor?

In this situation, the program stops processing with the following errors:

```NOTE: The data set WORK.TEST has 1 observations and 5 variables. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds   ERROR: Invalid characters were present in the data. ERROR: An error occurred while processing text data. NOTE: The SAS System stopped processing this step because of errors.   NOTE: There were 1 observations read from the data set WORK.TEST.```

In this case, the HTML Results tab does not display a last name at all.

To eliminate this error, you need to use the ENCODING= option in the %INCLUDE statement, as shown below.

`%include "your-directory/pgm.sas" /encoding="windows-1252";`

By including the ENCODING="windows-1252" option in the %INCLUDE statement, the program now executes successfully, as shown by the notes in the log:

``` NOTE: The data set WORK.TEST has 1 observations and 5 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.01 seconds     NOTE: There were 1 observations read from the data set WORK.TEST.```

### Happily Ever After (or, The End)!

The moral of this story is that there are many ways to avoid transcoding problems when you have national language characters in SAS programs that you save from a SAS®9 (English) session and move to a UTF-8 environment. Hopefully, you can use the tips that are provided to avoid such issues. However, if you still have problems, you can call on another hero, SAS Technical Support, for help!

### Epilogue

The following program is the one used throughout this story. You can copy and paste it for your own use.

```data test; input StudID \$12. Age Fname \$6. Mi :\$2. Lname \$7.; datalines; 120400310496 15 Oliver S. Füβling ;   proc print; run;```

### Referencing tables

With the SAS language the way one references a table in code is by using a two-level name or a one-level name. With two-level names, one supplies the libref as well as the table name i.e. PROC MEANS DATA = WORK.TABLE;. By default, all one-level names also refer to SASWORK i.e. PROC MEANS DATA = TABLE;

### CAS

To reference CAS tables using a one-level name we will issue two statements that alter which libref houses the tables referenced as one-level names. First, we will create a CAS libref (line 77) followed by the “options user” statement on line 80. It is line 80 that changes the default location from SASWORK to the CAS libref i.e. CASWORK.Figure 1. Statements to alter the default location for one-level names

### How to reference one-level CAS tables

From this point on all one-level names referenced in code will be tables managed by CAS. In figure 2 we are creating a one-level CAS table called baseball by reading the two-level named table SASHELP.BASEBALL. This step executes in the SAS Programing Runtime Engine (a SAS Viya based workspace server) and creates the table CASWORK.BASEBALL. Because of the “options user” statement we can now also reference that table using a one-level name i.e. BASEBALL.

Figure 2. Loading a two-level named table into a one-level named table that is managed by CAS

In Figure 3 we will use a DATA Step to read a CAS table and write a CAS table using one-level names. We can also see by reviewing the notes in the SAS log that this DATA Step ran in CAS using multiple threads.

Figure 3. DATA Step referencing one-level named tables

In Figure 4 we observe this PROC MEANS is processing the one-level named table BASEBALL. By reviewing the notes in the SAS log we can see this PROC MEANS ran distributed in CAS.

Figure 4. PROC MEANS referencing one-level named CAS table

Because the default location for one-level names is SASWORK, all tables in SASWORK are automatically deleted when the SAS session ends. When one changes the default location for one-level names, like we just did, it is a best practice to use a PROC DELETE as the last statement in your code to delete all one-level tables managed by CAS, figure 5.

Figure 5. PROC DELETE deleting all one-level CAS tables

### Conclusion

It is a very common SAS coding technique to read a source table from a non-SAS data store and write it to SASWORK. By using the technique describe in this blog one now has options on where to store the one-level tables names. As for me, I prefer storing them in CAS so I benefit from the distributed process (faster runtimes) that CAS offers.

How to reference CAS tables using a one-level name was published on SAS Users.

As a follow up to my previous blog, I want to address connecting to SAS Viya 3.3 using a One-Time-Password generated by SAS 9.4. I will talk about how this authentication flow operates and when we are likely to require it.

To start with, a One-Time-Password is generated by a SAS 9.4 Metadata Server when we connect to a resource via the metadata. For example, whenever we connect to the SAS 9.4 Stored Process Server we leverage a One-Time-Password. Sometimes this is referred to as a “trusted connection,” in that the resource we are connecting to is configured to “trust” the single-use credential generated by the SAS 9.4 Metadata Server.

To make the connection, the client application connects to the SAS 9.4 Metadata Server and requests the One-Time-Password (OTP). This OTP is sent by the client to the resource along with the username that has “@!*(generatedpassworddomain)*!” appended to it. The resource then connects back to the SAS 9.4 Metadata Server to validate the OTP and allow access.

### What Does OTP mean for SAS Viya?

First and foremost, we cannot use the OTP to access the SAS Viya 3.3 Visual Interfaces. OTP is not a mechanism to allow SAS Viya 3.3 to be authenticated by SAS 9.4.

The One-Time-Password enables a process running in SAS 9.4 Maintenance 5 (M5), that does not have the end-user credentials, to access SAS Cloud Analytic Services running on SAS Viya 3.3. The easiest and clearest example is that a SAS 9.4 M5 Stored Process can now access the advanced analytics features of SAS Cloud Analytic Services. Equally, the same process would work with a SAS 9.4 M5 Workspace Server that has been configured for “trusted authentication,” where the operating system process runs as a launch credential rather than the end user.

### How Does the OTP Work?

If we continue the example of a SAS 9.4 M5 Stored Process, the SAS code in the Stored Process includes a CAS statement or CAS LIBNAME. In the CAS statement the authdomain is specified as _sasmeta_; this tells the Stored Process to connect to SAS 9.4 M5 Metadata to obtain credentials. The SAS 9.4 M5 Metadata returns a One-Time-Password to the Stored Process and this is used in the connection to SAS Cloud Analytic Services.

SAS Cloud Analytic Services authenticates the incoming connection using the OTP. Since the user is flagged with “@!*(generatedpassworddomain)*!” SAS Cloud Analytic Services knows not to authenticate the user against the PAM stack on the host. SAS Cloud Analytic Services instead connects to the SAS Viya 3.3 SAS Logon Manager to obtain an internal OAuth token to authenticate the connection.

The SAS Viya 3.3 SAS Logon Manager has been configured with information about the SAS 9.4 M5 environment, specifically, the host running the SAS Web Infrastructure Platform, in the form of a URL. Since the user is “@!*(generatedpassworddomain)*!”, SAS Viya 3.3 SAS Logon Manager knows to send this to the SAS 9.4 M5 Web Infrastructure Platform to validate the OTP. Once the OTP is validated, the SAS Viya 3.3 Logon Manager can generate an internal OAuth token, including retrieving the end-users group information from the Identities microservice. This internal OAuth token is returned to SAS Cloud Analytic Services and the session launched.

The diagram below describes these steps:

The general steps include:

1.     The SAS 9.4 M5 SAS Server, running with a launch credential (Stored Process, Pooled Workspace, or Workspace Server) requests a One-Time Password from the Metadata Server for the connection to SAS Cloud Analytic Services.
2.     The SAS 9.4 M5 SAS Server connects to the CAS Server Controller, sending the One-Time Password.
3.     The CAS Controller connects to SAS Logon Manager to obtain an internal OAuth token using the One-Time Password.
4.     SAS Logon Manager connects via the SAS 9.4 M5 Middle-Tier to validate the One-Time Password.
5.     SAS 9.4 M5 Middle-Tier connects to the Metadata Server to validate the One-Time Password.
6.     SAS Logon Manager connects to the identities microservice to fetch custom and LDAP group information for the validated End-User.
7.     The identities microservice either looks up the validated End-User in its cache or connects to Active Directory using the LDAP Service Account to update the cache.
8.     SAS Logon Manager returns a valid internal OAuth token to the SAS CAS Server Controller.
9.     SAS CAS Server Controller launches the CAS Session Controller as the service account for the End-User.

Note that none of the processes are running as the end-user. The SAS 9.4 process is running with a launch credential, either sassrv or some other account, whilehe SAS Cloud Analytic Services session runs as the account starting the SAS Cloud Analytic Services process, by default the CAS account.

### What do we need to configure?

Now that we understand how the process operates, we can look at what we need to configure to make this work correctly. We need to make changes on both the SAS 9.4 M5 side and the SAS Viya 3.3 side. For SAS 9.4 M5 we need to:

1.     Register the SAS CAS Server in Metadata. As of SAS 9.4 M5, the templates for adding a server include SAS Cloud Analytic Services.
2.     Optionally we might also register libraries against the SAS CAS Server in the SAS 9.4 M5 Metadata.

For SAS Viya 3.3 we need to:

1.     Configure SAS Logon Manager with the information about the SAS 9.4 M5 Web Infrastructure Platform, under sas.logon.sas9, as shown below.
2.     Ensure the usernames from SAS 9.4 M5 are the same as those returned by the SAS Identities microservice.

The SAS Viya 3.3 SAS Logon Manager will need to be restarted after adding the definition shown here:

### Conclusion

By leveraging the One-Time-Password, we make the power of SAS Cloud Analytic Services directly available to a wider range of SAS 9.4 M5 server process. This means our end-users, whether they are using SAS Stored Process Server, Pooled Workspace Server, or even a Workspace Server using a launched credential, can now directly access SAS Cloud Analytic Services.

SAS Viya connecting with SAS 9.4 One-Time-Passwords was published on SAS Users.

A question recently popped up in a discussion forum about creating table plots using SAS. So I thought I'd dabble in this topic, and see what I could come up with. If you're interested in tableplots, or American Community Survey (ACS) data, or data visualization, I invite you to follow [...]

The post Tableplots and ACS data appeared first on SAS Learning Post.

More than 3.5 percent of the world’s population is on the move, considered international migrants. That’s more than 250 million people living in a country different than their country of birth or nationality. To put that another way, if all migrants lived in a single country, their population would be [...]

More than 3.5 percent of the world’s population is on the move, considered international migrants. That’s more than 250 million people living in a country different than their country of birth or nationality. To put that another way, if all migrants lived in a single country, their population would be [...]

I've said it before: spreadsheets are not databases. However, many of us use spreadsheets as if they were databases, and then we struggle when the spreadsheet layout does not support database-style rigor of predictable rows, columns, and variable types -- the basic elements we need for analytics and reporting. If you're using SAS to read data from Microsoft Excel, what can you do when the data you need doesn't begin at cell A1?

By design, SAS can read data from any range of cells in your spreadsheet. In this article, I'll describe how to use the RANGE statement in PROC IMPORT to get the data you need.

With SAS 9.4 and later, SAS recommends using DBMS=XLSX for the most flexibility. It works on all operating systems without the need for additional components like the PC Files Server. Your Excel file must be in the Excel 2007-or-later format (XLSX). You do need a licence for SAS/ACCESS to PC Files. (Just learning? These DBMS=XLSX techniques also work in SAS University Edition.)

If your Excel data does not begin in cell A1 (the default start point for an import process), then you can add a RANGE= value that includes the specific cells. The easiest method is to use a Named Range in Excel to define the exact boundaries of the data.

### How to add a Named Range

To define a named range in Excel, highlight the range of cells to include and simply type the new name of the range in the Name Box:

Then save the Excel file.

Then to import into SAS, specify that range name in the RANGE= option:

```proc import datafile="/myprojects/myfile.xlsx" out=mydata replace; range="myspecialrange"; run;```

### Using Excel notation for a cell range

What if you don't know the range ahead of time? You can use PROC IMPORT to read the entire sheet, but the result will not have the column headers and types you want. Consider a sheet like this:

```proc import datafile="/myprojects/middle.xlsx" out=mid dbms=xlsx replace; run;```

But the result will contain many empty cells, and the values will be read as all character types:

With additional coding, you can "fix" this result in another pass using DATA step. Or, if you're willing to add the RANGE option with the Excel notation for the specific cell ranges, you can read it properly in the first pass:

```proc import datafile="/myprojects/middle.xlsx" out=mid dbms=xlsx replace; range="Sheet1\$E7:K17" ; run;```

### How to "discover" the structure of your Excel file

You can also use LIBNAME XLSX to read entire sheets from Excel, or simply as a discovery step to see what sheets the Excel file contains before you run PROC IMPORT. However, LIBNAME XLSX does not show the Excel named ranges.

On SAS for Windows systems, you can use LIBNAME EXCEL (32-bit) or LIBNAME PCFILES (64-bit) to reveal a little more information about the Excel file.

```libname d pcfiles path="c:\myprojects\middle.xlsx"; proc datasets lib=d; quit;   /* always clear the libname, as it locks the file */ libname d clear;```

Note that DBMS=XLSX does not support some of the options we see in the legacy DBMS=XLS (which supports only old-format XLS files), such as STARTROW and NAMEROW. DBMS=XLSX does support GETNAMES (treats the first record of the sheet or range as the variable names). See the full reference for Excel file import/export in the SAS documentation.

The post How to use SAS to read a range of cells from Excel appeared first on The SAS Dummy.