Problem Solvers

7月 302020
 
How often have you needed Google Translate, but for SAS code?

SAS Technical Support often gets requests like the following: "I have this API named <insert name of really cool API here> and I want to process data I get back from the API with SAS. How do I do it?"

In this article, I'll show you how to translate the examples from your API documentation (offered in a select few languages) to the equivalent in SAS.

Test your API with cURL

My first recommendation is to know your API. Most APIs come with documentation and will give specific guidance regarding how to POST data (send) to the API and how to GET data (receive) from the API. Often, examples are provided using cURL commands.

With this information, you are welcome to examine the SAS documentation for the HTTP procedure and build your code. Before you call or email SAS Technical Support asking for PROC HTTP code, I encourage you to verify that you can communicate with your API (or URL) from outside of SAS. One way to do so is with cURL. cURL (Client URL) is a command-line tool that is shipped with many UNIX flavors and installed easily on Windows. With a cURL command, you can interact with your URLs from outside of SAS from a command-line prompt like this:

curl -o "c:\temp\file.txt" -request "https://httpbin.org/get"

From SAS, you can use a cURL command with the DATA step, like this:

data _null_;
 %sysexec curl -o "c:\temp\file.txt" -request "https://httpbin.org/get";
run;

File.txt contains the response from the URL:

{
  "args": {}, 
  "headers": {
    "Accept": "*/*", 
    "Host": "httpbin.org", 
    "Range": "bytes=Request", 
    "User-Agent": "curl/7.46.0", 
    "X-Amzn-Trace-Id": "Root=1-5f028cd3-2ec7e1e05da1f616e9106ee8"
  }, 
  "origin": "149.173.8.1", 
  "url": "https://httpbin.org/get"
}

However, if you use SAS® Enterprise Guide® or SAS® Studio, you might not have permissions to run operating system commands from SAS, so you need a way to translate your cURL commands to SAS. The previous cURL command is easily translated to the following PROC HTTP code:

filename out "c:\temp\file.txt";
proc http url="https://httpbin.org/get" out=out;
run;
  1. The -o (OUTPUT) cURL argument translates to the OUT= argument in PROC HTTP.
  2. The -request argument defaults to a GET for cURL (also the default for PROC HTTP, so METHOD=“GET” is the correct syntax but unnecessary for this step).
  3. Note: The URL= argument is always quoted.

The cURL command supports many options and features. Check out the cURL reference page. SAS can't guarantee that all are directly translatable to PROC HTTP, but I do want to cover some of the most popular that SAS customers have asked about.

Sending data to an API

If your cURL command uses the -d (DATA) option, you'll use the IN= argument in your PROC HTTP statement. Here I am posting to the URL httpbin.org/post a file called test.csv, which resides in my c:\temp directory:

curl -d "c:\temp\test.csv" -X post "https://httpbin.org/post";

This command translates to the following PROC HTTP code:

filename test "c:\temp\test.csv";
 
proc http url="https://httpbin.org/post"
 /* If the IN= argument is used then method="post" is the default */ 
 /* and therefore unnecessary in this step */
 method="post"
 in=test;
run;

Working with authentication

None of the URLs above require authentication, but you'll likely find authentication is part of most APIs. Many APIs have moved to OAuth for authentication. This method of authentication requires the use of an access token, which you obtain with a POST command. With the correct credentials, this cURL command posts to the SAS® Viya® SASLogon REST API in order to obtain an access token:

 
curl -X POST "https://server.example.com/SASLogon/oauth/token" \
      -H "Content-Type: application/x-www-form-urlencoded" \
      -d "grant_type=password&username=userid&password=mypassword" \
      -u "app:mysecret" -o "c:\temp\token.txt"

The following PROC HTTP code does the same task:

 filename out temp;
 proc http url="http://servername/SASLogon/oauth/token"
    in="grant_type=password&username=userid&password=mypassword"
    webusername="clientid"
    webpassword="clientsecret"
    method="post"
    out=out;
   headers "Content-Type"="application/x-www-form-urlencoded";
run;
  1. The -u option is for user ID and password.*
  2. The -o command/output captures the response, in this case a JSON file. In this case you mimic -o with a FILENAME statement to write the text in JSON format to the WORK library location.
  3. The -H command is popular and translates to the HEADERS statement in PROC HTTP.

*Read about ways to hide your credentials in Chris Hemedinger's post here: How to secure your REST API credentials in SAS programs.

The output file contains an access token, necessary to make requests on behalf of a client to the REST API. In this example, a cURL command like the following requests a list of folders from the Folders microservice:

curl -X GET "https://server.example.com/folders/folders/@myFolder" \
      -H "Accept: application/json" \
      -H "Authorization: Bearer TOKEN-STRING"

The PROC HTTP code will look like this if you "directly translate":

filename new temp;
 
proc http url=" https://server.example.com/folders/folders/@myFolder"
 method="get" out=new;
 headers "Accept"="application/json" 
         "Authorization"="Bearer TOKEN-STRING";
run;

But starting in SAS® 9.4M5, there's a shortcut with the OAUTH_BEARER option:

filename new temp;
 
proc http OAUTH_BEARER="TOKEN-STRING" 
 url="https://server.example.com/folders/folders/@myFolder" 
 method="get" 
 out=new;
run;

Processing JSON responses with the JSON engine

I can't tell you about PROC HTTP without a mention of the JSON engine. Starting in SAS® 9.4m4, the JSON engine enables us to easily read JSON files. I can use the previous cURL command to pipe my access token to a file with the -o argument, but using my PROC HTTP code I can easily move that value into a macro variable. I'll add a LIBNAME statement that points to the fileref in the previous step:

libname test json fileref=new;

I can then examine the contents of the JSON output with this step:

proc contents data=test._all_;
run;

Here I spy the access token I will need for a later PROC HTTP step:

Here's how I can place it in a macro variable:

data _null_;
 set test.root;
 call symputx("access_token",access_token);
run;
 
%put &access_token;

So everywhere I used TOKEN-STRING in the previous code, I can now use the macro variable instead, like this:

proc http OAUTH_BEARER="&access_token" 
 url="https://server.example.com/folders/folders/@myFolder" 
 method="get" 
 out=new;
run;

Debugging with PROC HTTP

With the cURL command, you can use the -v (verbose) argument to get connection and header information. It's helpful for debugging and diagnosing trouble spots.

In SAS® 9.4M5, the DEBUG statement was added to PROC HTTP. DEBUG supports several options. I'll highlight the LEVEL= option here:

 Level= 0 | 1 | 2 | 3

Selecting 0 provides no debugging information while 3 provides the highest amount of data and messages. Base SAS 9.4 Procedures Guide includes full descriptions of each debug level.

See the HTTP Procedure documentation for additional syntax.

Generating cURL commands with Postman

If you want someone to "write your code for you," I recommend using a product like Postman to test your POST and GET commands with your API from outside of SAS. Postman is an open-source product with a super cool feature: it will produce CURL commands from successful communication with a URL.

Recommended resources

How to translate your cURL command into SAS code was published on SAS Users.

7月 302020
 
How often have you needed Google Translate, but for SAS code?

SAS Technical Support often gets requests like the following: "I have this API named <insert name of really cool API here> and I want to process data I get back from the API with SAS. How do I do it?"

In this article, I'll show you how to translate the examples from your API documentation (offered in a select few languages) to the equivalent in SAS.

Test your API with cURL

My first recommendation is to know your API. Most APIs come with documentation and will give specific guidance regarding how to POST data (send) to the API and how to GET data (receive) from the API. Often, examples are provided using cURL commands.

With this information, you are welcome to examine the SAS documentation for the HTTP procedure and build your code. Before you call or email SAS Technical Support asking for PROC HTTP code, I encourage you to verify that you can communicate with your API (or URL) from outside of SAS. One way to do so is with cURL. cURL (Client URL) is a command-line tool that is shipped with many UNIX flavors and installed easily on Windows. With a cURL command, you can interact with your URLs from outside of SAS from a command-line prompt like this:

curl -o "c:\temp\file.txt" -request "https://httpbin.org/get"

From SAS, you can use a cURL command with the DATA step, like this:

data _null_;
 %sysexec curl -o "c:\temp\file.txt" -request "https://httpbin.org/get";
run;

File.txt contains the response from the URL:

{
  "args": {}, 
  "headers": {
    "Accept": "*/*", 
    "Host": "httpbin.org", 
    "Range": "bytes=Request", 
    "User-Agent": "curl/7.46.0", 
    "X-Amzn-Trace-Id": "Root=1-5f028cd3-2ec7e1e05da1f616e9106ee8"
  }, 
  "origin": "149.173.8.1", 
  "url": "https://httpbin.org/get"
}

However, if you use SAS® Enterprise Guide® or SAS® Studio, you might not have permissions to run operating system commands from SAS, so you need a way to translate your cURL commands to SAS. The previous cURL command is easily translated to the following PROC HTTP code:

filename out "c:\temp\file.txt";
proc http url="https://httpbin.org/get" out=out;
run;
  1. The -o (OUTPUT) cURL argument translates to the OUT= argument in PROC HTTP.
  2. The -request argument defaults to a GET for cURL (also the default for PROC HTTP, so METHOD=“GET” is the correct syntax but unnecessary for this step).
  3. Note: The URL= argument is always quoted.

The cURL command supports many options and features. Check out the cURL reference page. SAS can't guarantee that all are directly translatable to PROC HTTP, but I do want to cover some of the most popular that SAS customers have asked about.

Sending data to an API

If your cURL command uses the -d (DATA) option, you'll use the IN= argument in your PROC HTTP statement. Here I am posting to the URL httpbin.org/post a file called test.csv, which resides in my c:\temp directory:

curl -d "c:\temp\test.csv" -X post "https://httpbin.org/post";

This command translates to the following PROC HTTP code:

filename test "c:\temp\test.csv";
 
proc http url="https://httpbin.org/post"
 /* If the IN= argument is used then method="post" is the default */ 
 /* and therefore unnecessary in this step */
 method="post"
 in=test;
run;

Working with authentication

None of the URLs above require authentication, but you'll likely find authentication is part of most APIs. Many APIs have moved to OAuth for authentication. This method of authentication requires the use of an access token, which you obtain with a POST command. With the correct credentials, this cURL command posts to the SAS® Viya® SASLogon REST API in order to obtain an access token:

 
curl -X POST "https://server.example.com/SASLogon/oauth/token" \
      -H "Content-Type: application/x-www-form-urlencoded" \
      -d "grant_type=password&username=userid&password=mypassword" \
      -u "app:mysecret" -o "c:\temp\token.txt"

The following PROC HTTP code does the same task:

 filename out temp;
 proc http url="http://servername/SASLogon/oauth/token"
    in="grant_type=password&username=userid&password=mypassword"
    webusername="clientid"
    webpassword="clientsecret"
    method="post"
    out=out;
   headers "Content-Type"="application/x-www-form-urlencoded";
run;
  1. The -u option is for user ID and password.*
  2. The -o command/output captures the response, in this case a JSON file. In this case you mimic -o with a FILENAME statement to write the text in JSON format to the WORK library location.
  3. The -H command is popular and translates to the HEADERS statement in PROC HTTP.

*Read about ways to hide your credentials in Chris Hemedinger's post here: How to secure your REST API credentials in SAS programs.

The output file contains an access token, necessary to make requests on behalf of a client to the REST API. In this example, a cURL command like the following requests a list of folders from the Folders microservice:

curl -X GET "https://server.example.com/folders/folders/@myFolder" \
      -H "Accept: application/json" \
      -H "Authorization: Bearer TOKEN-STRING"

The PROC HTTP code will look like this if you "directly translate":

filename new temp;
 
proc http url=" https://server.example.com/folders/folders/@myFolder"
 method="get" out=new;
 headers "Accept"="application/json" 
         "Authorization"="Bearer TOKEN-STRING";
run;

But starting in SAS® 9.4M5, there's a shortcut with the OAUTH_BEARER option:

filename new temp;
 
proc http OAUTH_BEARER="TOKEN-STRING" 
 url="https://server.example.com/folders/folders/@myFolder" 
 method="get" 
 out=new;
run;

Processing JSON responses with the JSON engine

I can't tell you about PROC HTTP without a mention of the JSON engine. Starting in SAS® 9.4m4, the JSON engine enables us to easily read JSON files. I can use the previous cURL command to pipe my access token to a file with the -o argument, but using my PROC HTTP code I can easily move that value into a macro variable. I'll add a LIBNAME statement that points to the fileref in the previous step:

libname test json fileref=new;

I can then examine the contents of the JSON output with this step:

proc contents data=test._all_;
run;

Here I spy the access token I will need for a later PROC HTTP step:

Here's how I can place it in a macro variable:

data _null_;
 set test.root;
 call symputx("access_token",access_token);
run;
 
%put &access_token;

So everywhere I used TOKEN-STRING in the previous code, I can now use the macro variable instead, like this:

proc http OAUTH_BEARER="&access_token" 
 url="https://server.example.com/folders/folders/@myFolder" 
 method="get" 
 out=new;
run;

Debugging with PROC HTTP

With the cURL command, you can use the -v (verbose) argument to get connection and header information. It's helpful for debugging and diagnosing trouble spots.

In SAS® 9.4M5, the DEBUG statement was added to PROC HTTP. DEBUG supports several options. I'll highlight the LEVEL= option here:

 Level= 0 | 1 | 2 | 3

Selecting 0 provides no debugging information while 3 provides the highest amount of data and messages. Base SAS 9.4 Procedures Guide includes full descriptions of each debug level.

See the HTTP Procedure documentation for additional syntax.

Generating cURL commands with Postman

If you want someone to "write your code for you," I recommend using a product like Postman to test your POST and GET commands with your API from outside of SAS. Postman is an open-source product with a super cool feature: it will produce CURL commands from successful communication with a URL.

Recommended resources

How to translate your cURL command into SAS code was published on SAS Users.

7月 302020
 
How often have you needed Google Translate, but for SAS code?

SAS Technical Support often gets requests like the following: "I have this API named <insert name of really cool API here> and I want to process data I get back from the API with SAS. How do I do it?"

In this article, I'll show you how to translate the examples from your API documentation (offered in a select few languages) to the equivalent in SAS.

Test your API with cURL

My first recommendation is to know your API. Most APIs come with documentation and will give specific guidance regarding how to POST data (send) to the API and how to GET data (receive) from the API. Often, examples are provided using cURL commands.

With this information, you are welcome to examine the SAS documentation for the HTTP procedure and build your code. Before you call or email SAS Technical Support asking for PROC HTTP code, I encourage you to verify that you can communicate with your API (or URL) from outside of SAS. One way to do so is with cURL. cURL (Client URL) is a command-line tool that is shipped with many UNIX flavors and installed easily on Windows. With a cURL command, you can interact with your URLs from outside of SAS from a command-line prompt like this:

curl -o "c:\temp\file.txt" -request "https://httpbin.org/get"

From SAS, you can use a cURL command with the DATA step, like this:

data _null_;
 %sysexec curl -o "c:\temp\file.txt" -request "https://httpbin.org/get";
run;

File.txt contains the response from the URL:

{
  "args": {}, 
  "headers": {
    "Accept": "*/*", 
    "Host": "httpbin.org", 
    "Range": "bytes=Request", 
    "User-Agent": "curl/7.46.0", 
    "X-Amzn-Trace-Id": "Root=1-5f028cd3-2ec7e1e05da1f616e9106ee8"
  }, 
  "origin": "149.173.8.1", 
  "url": "https://httpbin.org/get"
}

However, if you use SAS® Enterprise Guide® or SAS® Studio, you might not have permissions to run operating system commands from SAS, so you need a way to translate your cURL commands to SAS. The previous cURL command is easily translated to the following PROC HTTP code:

filename out "c:\temp\file.txt";
proc http url="https://httpbin.org/get" out=out;
run;
  1. The -o (OUTPUT) cURL argument translates to the OUT= argument in PROC HTTP.
  2. The -request argument defaults to a GET for cURL (also the default for PROC HTTP, so METHOD=“GET” is the correct syntax but unnecessary for this step).
  3. Note: The URL= argument is always quoted.

The cURL command supports many options and features. Check out the cURL reference page. SAS can't guarantee that all are directly translatable to PROC HTTP, but I do want to cover some of the most popular that SAS customers have asked about.

Sending data to an API

If your cURL command uses the -d (DATA) option, you'll use the IN= argument in your PROC HTTP statement. Here I am posting to the URL httpbin.org/post a file called test.csv, which resides in my c:\temp directory:

curl -d "c:\temp\test.csv" -X post "https://httpbin.org/post";

This command translates to the following PROC HTTP code:

filename test "c:\temp\test.csv";
 
proc http url="https://httpbin.org/post"
 /* If the IN= argument is used then method="post" is the default */ 
 /* and therefore unnecessary in this step */
 method="post"
 in=test;
run;

Working with authentication

None of the URLs above require authentication, but you'll likely find authentication is part of most APIs. Many APIs have moved to OAuth for authentication. This method of authentication requires the use of an access token, which you obtain with a POST command. With the correct credentials, this cURL command posts to the SAS® Viya® SASLogon REST API in order to obtain an access token:

 
curl -X POST "https://server.example.com/SASLogon/oauth/token" \
      -H "Content-Type: application/x-www-form-urlencoded" \
      -d "grant_type=password&username=userid&password=mypassword" \
      -u "app:mysecret" -o "c:\temp\token.txt"

The following PROC HTTP code does the same task:

 filename out temp;
 proc http url="http://servername/SASLogon/oauth/token"
    in="grant_type=password&username=userid&password=mypassword"
    webusername="clientid"
    webpassword="clientsecret"
    method="post"
    out=out;
   headers "Content-Type"="application/x-www-form-urlencoded";
run;
  1. The -u option is for user ID and password.*
  2. The -o command/output captures the response, in this case a JSON file. In this case you mimic -o with a FILENAME statement to write the text in JSON format to the WORK library location.
  3. The -H command is popular and translates to the HEADERS statement in PROC HTTP.

*Read about ways to hide your credentials in Chris Hemedinger's post here: How to secure your REST API credentials in SAS programs.

The output file contains an access token, necessary to make requests on behalf of a client to the REST API. In this example, a cURL command like the following requests a list of folders from the Folders microservice:

curl -X GET "https://server.example.com/folders/folders/@myFolder" \
      -H "Accept: application/json" \
      -H "Authorization: Bearer TOKEN-STRING"

The PROC HTTP code will look like this if you "directly translate":

filename new temp;
 
proc http url=" https://server.example.com/folders/folders/@myFolder"
 method="get" out=new;
 headers "Accept"="application/json" 
         "Authorization"="Bearer TOKEN-STRING";
run;

But starting in SAS® 9.4M5, there's a shortcut with the OAUTH_BEARER option:

filename new temp;
 
proc http OAUTH_BEARER="TOKEN-STRING" 
 url="https://server.example.com/folders/folders/@myFolder" 
 method="get" 
 out=new;
run;

Processing JSON responses with the JSON engine

I can't tell you about PROC HTTP without a mention of the JSON engine. Starting in SAS® 9.4m4, the JSON engine enables us to easily read JSON files. I can use the previous cURL command to pipe my access token to a file with the -o argument, but using my PROC HTTP code I can easily move that value into a macro variable. I'll add a LIBNAME statement that points to the fileref in the previous step:

libname test json fileref=new;

I can then examine the contents of the JSON output with this step:

proc contents data=test._all_;
run;

Here I spy the access token I will need for a later PROC HTTP step:

Here's how I can place it in a macro variable:

data _null_;
 set test.root;
 call symputx("access_token",access_token);
run;
 
%put &access_token;

So everywhere I used TOKEN-STRING in the previous code, I can now use the macro variable instead, like this:

proc http OAUTH_BEARER="&access_token" 
 url="https://server.example.com/folders/folders/@myFolder" 
 method="get" 
 out=new;
run;

Debugging with PROC HTTP

With the cURL command, you can use the -v (verbose) argument to get connection and header information. It's helpful for debugging and diagnosing trouble spots.

In SAS® 9.4M5, the DEBUG statement was added to PROC HTTP. DEBUG supports several options. I'll highlight the LEVEL= option here:

 Level= 0 | 1 | 2 | 3

Selecting 0 provides no debugging information while 3 provides the highest amount of data and messages. Base SAS 9.4 Procedures Guide includes full descriptions of each debug level.

See the HTTP Procedure documentation for additional syntax.

Generating cURL commands with Postman

If you want someone to "write your code for you," I recommend using a product like Postman to test your POST and GET commands with your API from outside of SAS. Postman is an open-source product with a super cool feature: it will produce CURL commands from successful communication with a URL.

Recommended resources

How to translate your cURL command into SAS code was published on SAS Users.

6月 232020
 

When you execute code on the SAS® Cloud Analytic Services (CAS) server, one way you can improve performance is by partitioning a table. A partitioned table enables the data to load faster when that data needs to be grouped by the common values of a variable.

This post explains what it means to partition a table and describes the advantages of a partitioned table. I'll illustrate these concepts with example code that shows improved processing time when you use a partitioned table.

SAS BY-group requires SORT

BY-group processing enables you to group your data by unique variable values. This processing is used, for example, in tasks like merging data sets by a common variable and producing reports that contain data that is grouped by the value of a classification variable. You also use BY-group processing when you execute code in CAS. A key difference in creating BY-groups in SAS versus in CAS is that SAS requires a SORT procedure to sort the data by the specified BY variable in order to create the BY groups.

BY-group sorting implicit in CAS

This step is not required in CAS. When you perform BY-group processing on a CAS table, an implicit sorting action takes place, and each BY-group is distributed to the available threads. This implicit sort process takes place each time that the table is accessed and the BY-groups are requested.

Partitioning a CAS table permanently stores the table such that the values of the BY variable are grouped. Using a partitioned CAS table enables you to skip the implicit sort process each time the table is used, which can greatly improve performance.

Partition CAS action example

You create a partitioned CAS table by using the partition CAS action. The following example shows how to partition the CARS table (created from the SASHELP.CARS data set) by the MAKE variable.

caslib _all_ assign;    ①
data casuser.cars;      
set sashelp.cars;  ②
run;
proc cas;
table.partition /     ③                                              
casout={caslib="casuser", name="cars2"} ④                            
table={caslib="casuser", name="cars", groupby={name="make"}}; ⑤
quit;

In this code:

①  The CASLIB statement creates SAS librefs that point to all existing caslibs. CASUSER, which is used in the subsequent DATA step, is one of the librefs that are created by this statement.

②  The DATA step creates the CARS CAS table in the CASUSER caslib.

③  The partition action in the CAS procedure is part of the TABLE action set.

④  The casout= parameter contains the caslib= parameter, which points to the caslib where the partitioned CAS table named CARS2 will be stored.

⑤ The table= parameter contains the name= parameter, which lists the name of the table that is being partitioned. It also contains the CASLIB= option, which points to the caslib in which the table is stored. The groupby= parameter contains the name= option, which names the variable by which to partition the table.

You can confirm that the table has been partitioned by submitting the following CAS procedure with the tabledetails action.

proc cas;
table.tabledetails result=r / level='partition' ①
name='cars2'
caslib='casuser';
run;
describe r; ②
quit;

In this code:
① The LEVEL= parameter specifies the aggregation level of the TABLEDETAILS output.
② The DESCRIBE statement writes the output of the TABLEDETAILS action to the log.

The following output is displayed in the resulting log. The KEY column shows the variable the table has been partitioned by.

As mentioned earlier, the purpose of partitioning a table is to improve performance. The following example uses two CAS tables that consist of an ID variable with 10 possible values and 10 character variables. Each table contains 5,000,000 rows. This example illustrates how much performance improvement you can gain by partitioning the tables. In this case, the ID variable merges two tables.

First, you create the tables by submitting the following DATA steps:

data casuser.one;
array vars(10) $8 x1-x10;
do j=1 to 5000000;
id=put(rand('integer',1,10),8.);
do i=1 to 10;
vars(i)=byte(rand('integer',65,90));
end;
output;
end;
drop i j;
run;
 
data casuser.two;
array vars(10) $8 x1-x10;
do j=1 to 5000000;
id=put(rand('integer',1,10),8.);
do i=1 to 10;
vars(i)=byte(rand('integer',65,90));
end;
output;
end;
drop i j;
run;

The DATA steps above show how to merge non-partitioned tables. In the log output shown below, you can see that the total, real time (highlighted) took almost 45 seconds to run.

Partitioned tables example

The next example runs the same DATA step code, but it uses partitioned tables. The first step is to partition the tables, as shown below:

proc cas;
table.partition /                                                   
casout={caslib="casuser", name="onepart"}                             
table={caslib="casuser", name="one", groupby={name="id"}};   
run; 
 
table.partition /                                                   
casout={caslib="casuser", name="twopart"}                             
table={caslib="casuser", name="two", groupby={name="id"}};   
quit;

To merge the two tables and to product the log, submit the following code. (The real time is highlighted in the log.)

data casuser.nopart;
merge casuser.onepart casuser.twopart;
by id;
run;

The output for this code is show below:

This time, the DATA Step took only 25.43 seconds to execute. That is a 43% improvement in execution time!

Partition for improved performance

If your analysis requires you to use the same table multiple times to perform BY-group processing, then I strongly recommend that you partition the table. As the last example shows, partitioning your table can greatly improve performance!

Partition your CAS tables to greatly improve performance was published on SAS Users.

5月 152020
 
This blog demonstrates how to modify your ODS HTML code to make your column headers “sticky,” or fixed in position. Using sticky headers is most beneficial when you have long tables on your web page and you want the column headers to stay in view while scrolling through the rest of the page. The ability to add sticky headers was added with CSS 2.1, with the cascading style sheet (CSS) position property and its sticky value. You might have seen this capability before CSS 2.1 because it was supported by WebKit, which is a browser engine that Apple developed and is used primarily in the Safari browser (In Safari, you use the position property with the value -webkit-sticky.) The position: sticky style property is supported in the latest versions of the major browsers, except for Internet Explorer. The FROZEN_HEADERS= option can be used with the TableEditor tagset; see the TableEditor tagset method below.

Before you start

Here is a brief explanation about the task that this blog helps you accomplish. Since the position: sticky style property is supported with the <TH> HTML tags within tables, it is very easy for you to add the position: sticky style for HTML tables that ODS HTML generates. When this CSS style attribute is added for the headers, the headers are fixed within the viewport, which is the viewable area. The content in the viewport is scrollable, as seen in the example output below.

In the past, JavaScript was the main tool for generating fixed headers that are compatible across browsers and devices. However, the position: sticky property has also made it easier to fix various other elements, such as footers, within the viewport on the web page. This blog demonstrates how to make the <TH> tag or .header class sticky but enable the rest of the web page to be scrolled. The techniques here work for both desktop and mobile applications. There are multiple ways to add this style. Choose the method that is most convenient for you.

Method 1: Use the HEADTEXT= option

This example uses the position: sticky style property for the .header class, which is added to the HEADTEXT= option in the ODS HTML statement. The .header class is added along with the position style property between the <HEAD> and </HEAD> tags, which is the header section of the web page. This method is very convenient. However, you are limited to 256 characters and you might want to add other CSS style properties. The position style property is added using the .header class name, which is used by ODS HTML to add style attributes to the column headers. As the name suggests, cascading elements cascade and enable elements with like names to be combined. In the following code example, the HEADTEXT= option uses a CSS rule with the .header class and the position: sticky property for the header section of the web page.

ods html path="c:\temp" file="sticky.html"
headtext="<style> .header {position: sticky;top:0}</style>";
 
proc print data=sashelp.cars;
run;
 
ods html close;

Here is what the output looks like:

Method 2: Use the STYLESHEET= option

You can also add the position: sticky property to the .header class from an external CSS file, which can be referenced in ODS HTML code by using the STYLESHEET= option with the (URL=) suboption. This method uses a CSS file as a basis for the formatting, unlike the first method above, which had applied the default HTMLBLUE style for the destination.

Another item worth mentioning in this second example is the grouping of the CSS class selectors, which match the style element names used with ODS and the TEMPLATE procedure. For example, the .body, .systemtitle, .header, .rowheader, and .data class selectors are added and grouped into the font-family style property. This method is also used for several of the other style properties below. The .data class adds some additional functionality worth discussing, such as the use of a pseudo style selector, which applies a different background color for even alternating rows. In the example below, the .class names and the template element names are the same. You should place the CSS style rules that are shown here in a file that is named sticky.css.

.body, .systemtitle, .header, .rowheader, .data { 
font-family: arial, sans-serif; 
}  
.systemtitle, .header, .rowheader { 
font-weight: bold
} 
.table, .header, .rowheader, .data { 
border-spacing: 0; 
border-collapse: collapse; 
border: 1px solid #606060;
} 
.table tbody tr:nth-child(even) td { 
background-color: #e0e0e0; 
color: black;
}
.header { 
background-color: #e0e0e0;
position: -webkit-sticky;
position: sticky;
top:0;
} 
.header, .rowheader, .data { 
padding: 5px 10px;
}

After you create that CSS file, you can use the ODS HTML statement with the STYLESHEET= option. In that option, the (URL=) suboption uses the sticky.css file as the basis for the formatting. Forgetting to add the (URL=) suboption re-creates a CSS file with the current template style that is being used.

ods html path="c:\temp" file="sticky.html"
   stylesheet=(url="sticky.css");
proc print data=sashelp.cars;
run; 
ods html close;

Here is what the output looks like:

The pseudo class selector in the CSS file indicated that even alternating rows for all <TD> tags would be colored with the background color gray. Also, the position: sticky property in the .header class fixed the position of the header within the viewport.

Method 3: Use the TableEditor tagset

A third method uses the TableEditor tagset, which enables sticky headers to be added by using options. Options are also applied to modify the style for the alternating even and odd rows as well as to have sortable headers.

/* Reference the TableEditor tagset from support.sas.com. */
filename tpl url "http://support.sas.com/rnd/base/ods/odsmarkup/tableeditor/tableeditor.tpl";
/* Insert the tagset into the search path for ODS templates. */
ods path(Prepend) work.templat(update);
%include tpl;
ods tagsets.tableeditor file="c:\output\temp.html" 
options(sticky_headers="yes"
sort="yes"
banner_color_even="#e0e0e0") style=htmlblue;
 
proc print data=sashelp.cars;
run;
 
ods tagsets.tableeditor close;

Here is what the output looks like:

In summary, this article describes how easy it is to add sticky headers to tables that are generated by using the ODS HTML destination. Adding fixed headers to any table allows the output to dynamically preserve the headers in the viewable area while scrolling through the table, allowing a much richer experience. Give it a try and let me know how it goes.

Learn More

How to Add Sticky Headers with ODS HTML was published on SAS Users.

5月 152020
 
This blog demonstrates how to modify your ODS HTML code to make your column headers “sticky,” or fixed in position. Using sticky headers is most beneficial when you have long tables on your web page and you want the column headers to stay in view while scrolling through the rest of the page. The ability to add sticky headers was added with CSS 2.1, with the cascading style sheet (CSS) position property and its sticky value. You might have seen this capability before CSS 2.1 because it was supported by WebKit, which is a browser engine that Apple developed and is used primarily in the Safari browser (In Safari, you use the position property with the value -webkit-sticky.) The position: sticky style property is supported in the latest versions of the major browsers, except for Internet Explorer. The FROZEN_HEADERS= option can be used with the TableEditor tagset; see the TableEditor tagset method below.

Before you start

Here is a brief explanation about the task that this blog helps you accomplish. Since the position: sticky style property is supported with the <TH> HTML tags within tables, it is very easy for you to add the position: sticky style for HTML tables that ODS HTML generates. When this CSS style attribute is added for the headers, the headers are fixed within the viewport, which is the viewable area. The content in the viewport is scrollable, as seen in the example output below.

In the past, JavaScript was the main tool for generating fixed headers that are compatible across browsers and devices. However, the position: sticky property has also made it easier to fix various other elements, such as footers, within the viewport on the web page. This blog demonstrates how to make the <TH> tag or .header class sticky but enable the rest of the web page to be scrolled. The techniques here work for both desktop and mobile applications. There are multiple ways to add this style. Choose the method that is most convenient for you.

Method 1: Use the HEADTEXT= option

This example uses the position: sticky style property for the .header class, which is added to the HEADTEXT= option in the ODS HTML statement. The .header class is added along with the position style property between the <HEAD> and </HEAD> tags, which is the header section of the web page. This method is very convenient. However, you are limited to 256 characters and you might want to add other CSS style properties. The position style property is added using the .header class name, which is used by ODS HTML to add style attributes to the column headers. As the name suggests, cascading elements cascade and enable elements with like names to be combined. In the following code example, the HEADTEXT= option uses a CSS rule with the .header class and the position: sticky property for the header section of the web page.

ods html path="c:\temp" file="sticky.html"
headtext="<style> .header {position: sticky;top:0}</style>";
 
proc print data=sashelp.cars;
run;
 
ods html close;

Here is what the output looks like:

Method 2: Use the STYLESHEET= option

You can also add the position: sticky property to the .header class from an external CSS file, which can be referenced in ODS HTML code by using the STYLESHEET= option with the (URL=) suboption. This method uses a CSS file as a basis for the formatting, unlike the first method above, which had applied the default HTMLBLUE style for the destination.

Another item worth mentioning in this second example is the grouping of the CSS class selectors, which match the style element names used with ODS and the TEMPLATE procedure. For example, the .body, .systemtitle, .header, .rowheader, and .data class selectors are added and grouped into the font-family style property. This method is also used for several of the other style properties below. The .data class adds some additional functionality worth discussing, such as the use of a pseudo style selector, which applies a different background color for even alternating rows. In the example below, the .class names and the template element names are the same. You should place the CSS style rules that are shown here in a file that is named sticky.css.

.body, .systemtitle, .header, .rowheader, .data { 
font-family: arial, sans-serif; 
}  
.systemtitle, .header, .rowheader { 
font-weight: bold
} 
.table, .header, .rowheader, .data { 
border-spacing: 0; 
border-collapse: collapse; 
border: 1px solid #606060;
} 
.table tbody tr:nth-child(even) td { 
background-color: #e0e0e0; 
color: black;
}
.header { 
background-color: #e0e0e0;
position: -webkit-sticky;
position: sticky;
top:0;
} 
.header, .rowheader, .data { 
padding: 5px 10px;
}

After you create that CSS file, you can use the ODS HTML statement with the STYLESHEET= option. In that option, the (URL=) suboption uses the sticky.css file as the basis for the formatting. Forgetting to add the (URL=) suboption re-creates a CSS file with the current template style that is being used.

ods html path="c:\temp" file="sticky.html"
   stylesheet=(url="sticky.css");
proc print data=sashelp.cars;
run; 
ods html close;

Here is what the output looks like:

The pseudo class selector in the CSS file indicated that even alternating rows for all <TD> tags would be colored with the background color gray. Also, the position: sticky property in the .header class fixed the position of the header within the viewport.

Method 3: Use the TableEditor tagset

A third method uses the TableEditor tagset, which enables sticky headers to be added by using options. Options are also applied to modify the style for the alternating even and odd rows as well as to have sortable headers.

/* Reference the TableEditor tagset from support.sas.com. */
filename tpl url "http://support.sas.com/rnd/base/ods/odsmarkup/tableeditor/tableeditor.tpl";
/* Insert the tagset into the search path for ODS templates. */
ods path(Prepend) work.templat(update);
%include tpl;
ods tagsets.tableeditor file="c:\output\temp.html" 
options(sticky_headers="yes"
sort="yes"
banner_color_even="#e0e0e0") style=htmlblue;
 
proc print data=sashelp.cars;
run;
 
ods tagsets.tableeditor close;

Here is what the output looks like:

In summary, this article describes how easy it is to add sticky headers to tables that are generated by using the ODS HTML destination. Adding fixed headers to any table allows the output to dynamically preserve the headers in the viewable area while scrolling through the table, allowing a much richer experience. Give it a try and let me know how it goes.

Learn More

How to Add Sticky Headers with ODS HTML was published on SAS Users.

5月 152020
 
This blog demonstrates how to modify your ODS HTML code to make your column headers “sticky,” or fixed in position. Using sticky headers is most beneficial when you have long tables on your web page and you want the column headers to stay in view while scrolling through the rest of the page. The ability to add sticky headers was added with CSS 2.1, with the cascading style sheet (CSS) position property and its sticky value. You might have seen this capability before CSS 2.1 because it was supported by WebKit, which is a browser engine that Apple developed and is used primarily in the Safari browser (In Safari, you use the position property with the value -webkit-sticky.) The position: sticky style property is supported in the latest versions of the major browsers, except for Internet Explorer. The FROZEN_HEADERS= option can be used with the TableEditor tagset; see the TableEditor tagset method below.

Before you start

Here is a brief explanation about the task that this blog helps you accomplish. Since the position: sticky style property is supported with the <TH> HTML tags within tables, it is very easy for you to add the position: sticky style for HTML tables that ODS HTML generates. When this CSS style attribute is added for the headers, the headers are fixed within the viewport, which is the viewable area. The content in the viewport is scrollable, as seen in the example output below.

In the past, JavaScript was the main tool for generating fixed headers that are compatible across browsers and devices. However, the position: sticky property has also made it easier to fix various other elements, such as footers, within the viewport on the web page. This blog demonstrates how to make the <TH> tag or .header class sticky but enable the rest of the web page to be scrolled. The techniques here work for both desktop and mobile applications. There are multiple ways to add this style. Choose the method that is most convenient for you.

Method 1: Use the HEADTEXT= option

This example uses the position: sticky style property for the .header class, which is added to the HEADTEXT= option in the ODS HTML statement. The .header class is added along with the position style property between the <HEAD> and </HEAD> tags, which is the header section of the web page. This method is very convenient. However, you are limited to 256 characters and you might want to add other CSS style properties. The position style property is added using the .header class name, which is used by ODS HTML to add style attributes to the column headers. As the name suggests, cascading elements cascade and enable elements with like names to be combined. In the following code example, the HEADTEXT= option uses a CSS rule with the .header class and the position: sticky property for the header section of the web page.

ods html path="c:\temp" file="sticky.html"
headtext="<style> .header {position: sticky;top:0}</style>";
 
proc print data=sashelp.cars;
run;
 
ods html close;

Here is what the output looks like:

Method 2: Use the STYLESHEET= option

You can also add the position: sticky property to the .header class from an external CSS file, which can be referenced in ODS HTML code by using the STYLESHEET= option with the (URL=) suboption. This method uses a CSS file as a basis for the formatting, unlike the first method above, which had applied the default HTMLBLUE style for the destination.

Another item worth mentioning in this second example is the grouping of the CSS class selectors, which match the style element names used with ODS and the TEMPLATE procedure. For example, the .body, .systemtitle, .header, .rowheader, and .data class selectors are added and grouped into the font-family style property. This method is also used for several of the other style properties below. The .data class adds some additional functionality worth discussing, such as the use of a pseudo style selector, which applies a different background color for even alternating rows. In the example below, the .class names and the template element names are the same. You should place the CSS style rules that are shown here in a file that is named sticky.css.

.body, .systemtitle, .header, .rowheader, .data { 
font-family: arial, sans-serif; 
}  
.systemtitle, .header, .rowheader { 
font-weight: bold
} 
.table, .header, .rowheader, .data { 
border-spacing: 0; 
border-collapse: collapse; 
border: 1px solid #606060;
} 
.table tbody tr:nth-child(even) td { 
background-color: #e0e0e0; 
color: black;
}
.header { 
background-color: #e0e0e0;
position: -webkit-sticky;
position: sticky;
top:0;
} 
.header, .rowheader, .data { 
padding: 5px 10px;
}

After you create that CSS file, you can use the ODS HTML statement with the STYLESHEET= option. In that option, the (URL=) suboption uses the sticky.css file as the basis for the formatting. Forgetting to add the (URL=) suboption re-creates a CSS file with the current template style that is being used.

ods html path="c:\temp" file="sticky.html"
   stylesheet=(url="sticky.css");
proc print data=sashelp.cars;
run; 
ods html close;

Here is what the output looks like:

The pseudo class selector in the CSS file indicated that even alternating rows for all <TD> tags would be colored with the background color gray. Also, the position: sticky property in the .header class fixed the position of the header within the viewport.

Method 3: Use the TableEditor tagset

A third method uses the TableEditor tagset, which enables sticky headers to be added by using options. Options are also applied to modify the style for the alternating even and odd rows as well as to have sortable headers.

/* Reference the TableEditor tagset from support.sas.com. */
filename tpl url "http://support.sas.com/rnd/base/ods/odsmarkup/tableeditor/tableeditor.tpl";
/* Insert the tagset into the search path for ODS templates. */
ods path(Prepend) work.templat(update);
%include tpl;
ods tagsets.tableeditor file="c:\output\temp.html" 
options(sticky_headers="yes"
sort="yes"
banner_color_even="#e0e0e0") style=htmlblue;
 
proc print data=sashelp.cars;
run;
 
ods tagsets.tableeditor close;

Here is what the output looks like:

In summary, this article describes how easy it is to add sticky headers to tables that are generated by using the ODS HTML destination. Adding fixed headers to any table allows the output to dynamically preserve the headers in the viewable area while scrolling through the table, allowing a much richer experience. Give it a try and let me know how it goes.

Learn More

How to Add Sticky Headers with ODS HTML was published on SAS Users.

5月 052020
 

When you route REPORT procedure output to any of the ODS destinations, you might want to apply style attributes to a column based on multiple variable values. To do that, you need to use CALL DEFINE statements in a COMPUTE block. This structure could then require complex logic and many CALL DEFINE statements to ensure that all combinations of the variable values and styles are applied appropriately. However, the STYLE/MERGE and STYLE/REPLACE attributes in the CALL DEFINE statement can simplify this process. They are useful when you have two or more COMPUTE blocks with CALL DEFINE statements and the CALL DEFINE statements refer to the same cell in a table.

Using the STYLE/MERGE attribute

The STYLE/MERGE attribute combines any new styles that are specified by the STYLE= argument with any existing styles that are already applied to values in the row or column. In this example, style attributes are applied to the Sex column. In the first COMPUTE block, if the value of Sex is F, the background color of the cell is yellow. In the second COMPUTE block for Age, if the value of Age is greater than 14, the color of the text in the Sex column is red. When STYLE/MERGE is used, that means that a yellow background that also has red text is used for any cell in the Sex column where the value is F and the corresponding Age is also greater than 14.

 
proc report data=sashelp.class; 
column sex age height weight;
define sex--weight / display;
 
compute sex;
if sex = 'F' then
 call define('sex', "style", "style=[background=yellow]"); 
endcomp;
 
compute age;
if age > 14 then
 call define('sex', "style/merge", "style=[color=red]"); 
endcomp;
run;

Here is the resulting output:

Using the STYLE/REPLACE attribute

The STYLE/REPLACE attribute replaces any existing styles for a row or column with the new styles that are specified by the STYLE= argument. In this example, style attributes are applied to the Sex column again. In the first COMPUTE block, if the value of Sex is F, the background color of the cell is yellow. In the second COMPUTE block for Age, if the value of Age is greater than 14, the color of the text in the Sex column is red. When STYLE/REPLACE is used, that means that red text only, without any background color, is used for any cell in the Sex column where the value is F and the corresponding Age is also greater than 14. The red-text style replaces the yellow background.

 
proc report data=sashelp.class; 
column sex age height weight;
define sex--weight / display;
 
compute sex;
if sex = 'F' then
 call define('sex', "style", "style=[background=yellow]"); 
endcomp;
 
compute age;
if age > 14 then	
 call define('sex', "style/replace", "style=[color=red]"); 
endcomp;
run;

Here is the resulting output:

The STYLE/MERGE and STYLE/REPLACE attributes are supported only in the CALL DEFINE statement in a COMPUTE block in PROC REPORT. These useful tools can simplify complex code and enable you to customize your PROC REPORT output with meaningful style choices.

Additional References

PROC REPORT: CALL DEFINE

Sample Note 43758: How to merge styles from multiple CALL DEFINE statements with PROC REPORT

Using STYLE/MERGE and STYLE/REPLACE in PROC REPORT was published on SAS Users.

1月 112020
 
In SAS 9.3 and earlier, the default value of the YEARCUTOFF= option is 1920. This default setting could trigger data integrity issues because any 2-digit years of "20" in dates will be assumed to occur in 1920 instead of 2020. If the intended year in the date is 2020, you must set the YEARCUTOFF= option to a value larger than 1920. Of course, the best alternative is to always specify date values with 4-digit years.

Luckily, SAS makes it easy to change the YEARCUTOFF= option so that it works best for your data. The default value for the YEARCUTOFF= option changed in SAS 9.4 to 1926. This change makes it easier for customers who are still using 2-digit years of "20" to make sure that the date is assigned to 2020. Let's review some of the frequently asked questions that customers ask about how SAS works with 2-digit years.

What is the YEARCUTOFF= option?

The YEARCUTOFF= option lets you specify which century SAS software should assign to dates with 2-digit years.

How do I specify the YEARCUTOFF= option in my SAS programs?

The option is specified in an OPTIONS statement. Here is an example:

options yearcutoff=1930;

You can also specify the option in an autoexec file or a config file. If you don't specify the YEARCUTOFF= option, the SAS system default is used. Remember that 1920 is the default for SAS 9.3 and earlier releases and 1926 is the default for SAS 9.4. (For reference, SAS 9.3 was released in 2011. The first release of SAS 9.4 was released in 2013.)

How does the YEARCUTOFF= option work?

The YEARCUTOFF= option specifies the first year of a 100-year window in which all 2-digit years are assumed to occur. For example, if the YEARCUTOFF= option is set to 1920, all 2-digit years are assumed to occur between 1920 and 2019. This means that two-digit years from 20 - 99 are assigned a century prefix of "19" and all 2-digit years from 00 - 19 have a century prefix of "20."

Which types of date values are affected by the YEARCUTOFF= option?

The YEARCUTOFF= option affects the interpretation of 2-digit years in the following cases:

  • Reading date values from external files
  • Specifying dates or year values in SAS functions
  • Specifying SAS date literals

The YEARCUTOFF= option does not influence the following cases:

  • Processing dates with 4-digit years
  • Processing dates already stored as SAS date values (the number of days since January 1, 1960)
  • Displaying dates with SAS date formats

Which value should I set the YEARCUTOFF= option to?

The optimal value depends on the range of dates in your data. The YEARCUTOFF= option should be set so that the 100-year range encompasses the range of your data values. In general, SAS recommends setting the YEARCUTOFF= option to a value equal to or slightly less than the first year in your data. For example, if the range of dates that you are processing is from 1930 - 2010, a YEARCUTOFF value of 1925 or 1930 would be appropriate. If you set YEARCUTOFF=1925, then all 2-digit years are assumed to be in the 100-year period from 1925 to 2024. If all the dates in your data fall within that range, they will be interpreted correctly.

What do I do if my dates with 2-digit years span more than 100 years?

The YEARCUTOFF= option cannot reliably assign centuries to 2-digit years if the range of dates for a variable is greater than 100 years. If the date ranges for a variable span more than 100 years, you must either specify the dates with 4-digit years or use DATA step logic to assign a century to each year (perhaps based on the value of another variable).

But why does the YEARCUTOFF= option allow only a 100-year span? If the YEARCUTOFF= option allowed for more than a 100-year span, there would be no way to determine which century a 2-digit year should have. For example, let’s assume that YEARCUTOFF=1950 with a 150-year span and your external data file had 2-digit years. In this scenario, your 150-year span would be from 1950 to 2100. Since you have 2-digit years, there would be no way to determine if the year 00 was meant to occur during 2000 or 2100.

How do I change the default setting for all the SAS users at my site?

Setting system default option values is usually done by a site SAS Installation Representative. The recommended method for setting a system default YEARCUTOFF= value is to specify the desired value in the system SAS configuration file. Note that even if you set a default value for all the users at your site, they can override the default value in their SAS programs, in personal autoexec files, in config files, by setting an environment variable, or when invoking SAS software.

How do I change the default setting for my own programs if I want a default that is different from the rest of the users at my site?

You can specify personal default values either in a personal configuration file or in an autoexec file. If you specify the value in a personal configuration file, the syntax depends on your operating system and is the same as that for setting the value in the system-wide configuration file on each system. If you use an autoexec file, you can specify the YEARCUTOFF= option in an OPTIONS statement.  Here is an example:

options yearcutoff=1930;

Additional Resources

  • YEARCUTOFF= System Option section in SAS® 9.3 System Options: Reference, Second Edition
  • YEARCUTOFF= System Option section in SAS® 9.4 System Options: Reference, Fifth Edition
  • SAS Note 46368, "The default value for the YEARCUTOFF= system option has changed in SAS® 9.4 and beyond"
  • SAS Note 65307, "You might encounter an issue in which 2-digit year dates have the wrong century in SAS® 9.3 and earlier releases"

Why does my SAS date have the wrong century? was published on SAS Users.

12月 102019
 
The DATA step has been around for many years and regardless of how many new SAS® products and solutions are released, the DATA step remains a popular way to create and manipulate SAS data sets. The SAS language contains a wide variety of approaches that provide an endless number of ways to accomplish a goal. Whether you are reshaping a data set entirely or simply assigning values to a new variable, there are numerous tips and tricks that you can use to save time and keystrokes. Here are a few that Technical Support offers to customers on a regular basis.

Writing file contents to the SAS® log

Perhaps you are reading a file and seeing “invalid data” messages in the log or you are not sure which delimiter is used between variables. You can use the null INPUT statement to read a small sample of the data. Specify the amount of data that you want to read by adjusting options in the INFILE statement. This example reads one record with 500 bytes:

   data _null_;
      infile 'path' recfm=f lrecl=500 obs=1;
      input;
      list;
   run;

The LIST statement writes the current record to the log. In addition, it includes a ruled line above the record so that it is easier to see the data for each column of the file. If the data contains at least one unprintable character, you will see three lines of output for each record written.

For example:

RULE:     ----+----1----+----2----+----3----+----4----+----5----+----6----+
 
1   CHAR  this is a test.123.dog.. 24
    ZONE  766726726276770333066600
    NUMR  48930930104534912394F7DA

The line beginning with CHAR describes the character that is represented by the two hexadecimal characters underneath. When you see a period in that line, it might actually be a period, but it often means that the hexadecimal characters do not map to a specific key on the keyboard. Also, column 15 for this data is a tab ('09'x). By looking at this record in the log, you can see that the first variable contains spaces and that the file is tab delimited.

Writing hexadecimal output for all input

Since SAS® 9.4M6 (TS1M6), the HEXLISTALL option in the LIST statement enables all lines of input data to be written in hexadecimal format to the SAS log, regardless of whether there are unprintable characters in the data:

   data mylib.new / hexlistall;

Removing “invalid data” messages from the log

When SAS reads data into a data set, it is common to encounter values that are invalid based on the variable type or the informat that is specified for a variable. You should examine the SAS log to assess whether the informat is incorrect or if there are some records that are actually invalid. After you ensure that the data is being read correctly, you can dismiss any “invalid data” messages by using double question mark (??) modifiers after the variable name in question:

   input date ?? mmddyy10.;

You can also use the question mark modifiers when you convert a character variable to numeric with the INPUT function:

   newdate=input(olddate,?? mmddyy10.);

The above syntax reads all values with the MMDDYY10. informat and then dismisses the notes to the log when some values for the OLDDATE variable are invalid.

Sharing files between UNIX and Microsoft Windows operating systems

The end-of-record markers in text files are different for UNIX and Windows operating systems. If you needed to share a file between these systems, the file used to need preprocessing in order to change the markers to the desired type or perhaps specify a delimiter. Now, the TERMSTR= option in the INFILE statement enables you to specify the end-of-record marker in the incoming file.

If you are working in a UNIX environment and you need to read a file that was created in a Windows environment, use the TERMSTR=CRLF option:

   infile 'file-specification'  termstr=crlf ;

If you are in a Windows environment and you need to read a file that was created in a UNIX environment, use this syntax:

   infile 'file-specification'  termstr=lf ;

Adapting array values from an ARRAY statement

The VNAME function makes it very convenient to use the variable names from an ARRAY statement. You most often use an ARRAY statement to obtain the values from numerous variables. In this example, the SQL procedure makes it easy to store the unique values of the variable Product into the macro variable &VARLIST and that number of values into the macro variable &CT (another easy tip). Within the DO loop, you obtain the names of the variables from the array, and those values from the array then become variable names.

   proc sql noprint;
      select distinct(product) into :varlist separated by ' '
      from one;
      select count(distinct product) into :ct
      from one;
   quit;
 
   …more DATA step statements…
   array myvar(&ct) $ &varlist;
      do i=1 to &ct;
         if product=vname(myvar(i)) then do;
         myvar(i)=left(put(contract,8.));
   end;
   …more DATA step statements…

Using a mathematical equation in a DO loop

A typical DO loop has a beginning and an end, both represented by integers. Did you know you can use an equation to process data more easily? Suppose that you want to process every four observations as one unit. You can run code similar to the following:

   …more DATA step statements…
   J+1;
   do i=((j*4)-3) to (j*4);
      set data-set-name point=I;
      …more DATA step statements…
   end;

Using an equation to point to an array element

With small data sets, you might want to put all values for all observations into a single observation. Suppose that you have a data set with four variables and six observations. You can create an array to hold the existing variables and also create an array for the new variables. Here is partial code to illustrate how the equation dynamically points to the correct new variable name in the array:

   array old(4) a b c d;
   array test (24) var1-var24;
   retain var1-var24;
   do i=1 to nobs;
      set w nobs=nobs point=i;
      do j=1 to 4;
      test(((i-1)*4)+j)=old(j);
      end;
   end;

Creating a hexadecimal reference chart

How often have you wanted to know the hexadecimal equivalent for a character or vice versa? Sure, you can look up a reference chart online, but you can also create one with a short program. The BYTE function returns values in your computer’s character set. The PUT statement writes the decimal value, hexadecimal value, and equivalent character to the SAS log:

   data a;
      do i=0 to 255;
      x=byte(i);
      put i=z3. i=hex2. x=;
   end;
   run;

When the resulting character is unprintable, such as a carriage return and line feed (CRLF) character, the X value is blank.

Conclusion

Hopefully, these new tips will be useful in your future programming. If you know some additional tips, please comment them below so that readers of this blog can add even more DATA step tips to their arsenal! If you would like to learn more about DATA step, check out these other blogs:

Old reliable: DATA step tips and tricks was published on SAS Users.