BASE SAS Programming – Using DLM and DSD Options Together




Hi fellow data analysts. We are having a lot of fun putting together these posts for “BASE SAS Tutorial” series. We certainly hope you are enjoying and learning something from them as well. We have covered some very important ground so far but we have a long way to go. In this post, we will be discussing a possible scenario where are demonstrate the use of the DLM and DSD option together.

We have seen features of the DSD option. It is important to note that they all work independent of each other. For instance, if we were to set the delimiter as “/” using the DLM option, an accompanying DSD option would still identify missing values between consecutive delimiters without resetting the default delimiter to comma. Let us look at the sample data we would be using for this demo.

Sample Data

The below provided sample SAS program is used to read in the above sample data.

data sample1;
infile ‘/folders/myshortcuts/myFolder/Sample data – CSV.txt’ dsd dlm = “/”;
input gender $ Age Height Weight;
run;

Note: We are using the DSD and DLM options simultaneously in order to read in data from a delimited file with the delimiter “/”. The DSD option is useful to identify the missing values in the data. The outputs generated from this code are:

Logs:

Output:

As can be seen in the above screenshots, the data is read in appropriately. This combination is used so often in a day to day scenario that it is a must to learn, understand and deploy as if it were your second nature. Often, folks get confused, but with practice it is fairly easy to use and implement. Keep practicing by creating other sample data sets and see how the codes responds to changes.

Please find relevant reference books in the sidebar of this post, if you should want to purchase the same for further studying. BASE SAS Certification guide and the Little SAS book are extremely useful for preparation and also act as a wonderful reference for SAS concepts.

Please sign up for our newsletter, so that we may keep you posted on the latest activity on our website and Youtube channel.



BASE SAS Programming – Import Delimited Files




Hello all. Nice to have you going strong with our “BASE SAS Tutorial’ series. In the previous post, we looked at how the SAS tool reads text files with space delimiter. Spaces being the default delimiter for the tool. However, in the real world scenario, data is never provided from any source with space as the delimiter value. Luckily, SAS allows a great deal of flexibility to read delimited files with a variety of options of different elements. Let us look at the sample program below where we try to import the same data as in the previous post, but with the “/” character as the delimiter instead of the the default space.

Data sample1;
infile ‘/folders/myshortcuts/myFolder/Sample data – delimiter.txt’ dlm = “/”;
input gender $ Age Height Weight;
run;

Snapshot of the data contained in the file;

The output of the code as it would appear in the log and the output tab:

Log:

Output Tab:

This is the most common method of importing data into any SAS tool that is available for end users. It is important to understand clearly how the simple code shared in this post is working so that it can be scaled for data files with more number of columns and complications. Please try it out and share your experience in the comments below. Please find relevant reference books in the sidebar, if you should want to purchase the same for further studying. Please sign up for our newsletter, so that we may keep you posted on the latest activity on our website and Youtube channel.




Audit Command Language – Recno Function




Welcome readers, to another post in the ‘Audit Command Language Tutorial for Beginners’ series. Hopefully you have been following along all the posts in the series to get the most benefit out of this website.

In this post we will be discussing the Recno function. This is a very basic and simple function to use in the Audit Command Language scripts. This function is used to provide the current line of each record in a data set.

Syntax: Recno()

Example in a script to create a new field using RECNO Function

OPEN EMPMAST
DELETE FIELD TEST_Field OK
DEFINE FIELD TEST_Field COMPUTED AS
recno()

Example in a workspace to create a new field using RECNO Function

TEST_Field Computed
recno()

See below the result of the RECNO function.

As you can see in the above screenshot the  Recno  function allows to create a column with the record numbers. Ths function is especially beneficial when we have to work with multiple data sets and wish to keep track of the original records. Also, if we have record numbers available in data sets, it is easier to back and trace values while designing or troubleshooting errors generated due to incorrect conditions or criteria applied in the Audit Command Language scripts. The use described for this function is a good practice but in many tools, such a function is not always available to implement this easily. The use of this function is demonstrated in the video below:

 

Give it a go and share your experiences. Please look through our various posts for ACL script examples and ACL scripts command. Please sign up for our newsletter, so that we may keep you posted on the latest activity on our website and Youtube channel.




Audit Command Language – Nested Scripts




Hi all, welcome to another post in the series ‘Audit Command Language Tutorial for Beginners’. We hope all our readers are able to follow along and able to learn something new with each new post. For all those joining us at this stage, please start at the top of the series to get the most benefit from these posts about Audit Command Language.

In this post we will be discussing an important concept of Nested scripts. This is the closest the Audit Command Language scripts come to the object oriented concept of programming. To be clear has nothing to do with the concept of objects as in C++ or Java. Rather, it is simply the modular approach to programming that is enabled with the nested scripts i.e. different scripts are performing specifically designed tasks so as to keep the program properly arranged.

Lets assume we have a data set is which lists details employee details. We can extract all sorts of information from such a data set. See example of data set below:

Here are a few pieces of information that can be  extracted from this data set. See below:

  1. Extract Country Codes in the data sets.
  2. Extract unique Work dept with total salaries paid across each dept.
  3. All employees hired before or after a certain date.
  4. All employee codes with total salaries paid.

Now all the above information can be extracted by scripting a single Audit Command Language script but it is possible that we may want to perform further analysis on data sets created for each of the above points and it would thus be more suitable to design the scripts individually for each such case. The layout of the scripts might look like as shown in the screenshot below:

All scripts labelled “Analysis” in the screenshot are each individual analysis case for any data set. Once all cases are scripted, the next step is to deign the flow of execution of the scripts. The flow can only be linear in nature in the Audit Command Language. Of course, the same script can be referred to as many times as required for any execution flow. At any stage in any script the flow of the calling other scripts can be done by using the Command ‘DO’. See ACL script example below:

Syntax: Do Analysis1

Simply using the “DO” command allows us to design the flow of any Audit Command Language project. Of course there are endless possibilities of flows that can be explored based on the type of analysis that you are undertaking. We have prepared a demo video showing how such project might look. See the video below:

 

Please keep practicing and feel free to reach out to us with your valuable feedback and comments. Please look through our various posts for ACL script examples and ACL scripts commands. Please sign up for our newsletter, so that we may keep you posted on the latest activity on our website and Youtube channel.

 




Audit Command Language – Recoffset Function




Welcome to another “cool” post in the series “Audit Command Language Tutorial for Beginners”. So far we have discussed various aspects such as functions, command/statements which are used for manipulating data to create new data columns/fields or new tables with additional information. All these functionalities are useful when the data is present in the correct format or straight forward in general. Very often, the data lines provided by clients are not straightforward and needs to be formatted before we can manipulate the data to draw meaningful analysis or conclusions.

For such instances Audit Command Language scripts provide further functionality to address data issues after importing into the ACL views and tables. In the next couple posts, we would be discussing two very important functions that are used to address these very issues. The first of these is the RecOffset function. This function allows the Audit Command Language tool, to read data from lines before or after the current line. Consider the data set in the screenshot below:

Lets say, we are trying to create a new field in this data set. The goal would be to create a new column where the values are taken from the column Dept Code but only from the next line. As shown in the screenshot below:

You can now imagine a case where the data lines are split into two or more rows and you are required to fetch the values of certain columns from the next or two rows down or even above the current data line. This can be accomplished by using the Recoffset Function. This function is used precisely for the reason as described above and shown in the screenshots above. Below is the syntax for this function as it used in Audit Command Language Scripts and Workspaces.

Syntax: RECOFFSET(Dept,1)

ACL Script example to create a new field using RECOFFSET Function

OPEN DEPT
DELETE FIELD TEST_Field OK
DEFINE FIELD TEST_Field COMPUTED AS
RECOFFSET(Dept,1)

Example in a workspace to create a new field using RECOFFSET Function

TEST_Field Computed
TEST_Field Computed
RECOFFSET(Dept,1)

This is a very important functionality provided in the Audit Command Language. It is crucial to understand the use of this function. It can be extremely handy in a tight spot when there is no other way to cleanse the data. The use of this function is also demonstrated in the video below:

 

Please keep practicing and feel free to reach out to us with your valuable feedback and comments. Please go through the website to review ACL script examples and ACL script commands sign up for our newsletter, so that we may keep you posted on the latest activity on our website and Youtube channel.




Audit Command Language – Between Function ()




Welcome to another post in the series ‘Audit Command Language Tutorial for Beginners’. A lot of important topics have been covered so far in these series of posts. We certainly hope, all our readers are benefiting and learning from these posts. For all those who have just stumbled on this website, we urge you to start at the beginner of this series so as to get full benefit.

In this post we will be discussing the Between function. This function is a logical function i.e. it returns the values as true or false. The Between function is used to test the simple condition of whether or not a certain value lies in a given range. This test could be applied for a numeric, date or a character series. This implies this function works with all three major data types supported by the Audit Command Language.

Syntax: BETWEEN(TEST VALUE ,MIN_RANGE, MAX_RANGE)

Example in a script to create a new field using BETWEEN Function

OPEN EMPMAST
DELETE FIELD TEST_Field OK
DEFINE FIELD TEST_Field COMPUTED AS
“TRUE” IF Between(HireDate,`19820101`,`19830101`)
“FALSE”

Example in a workspace to create a new field using BETWEEN Function

TEST_Field Computed
“TRUE” IF Between(HireDate,`19820101`,`19830101`)
“FALSE”

See below the result of the BETWEEN function.

Easy enough to use…yes? Just a couple pf things to remember when using this function. The Between function is always used as part of the conditional statement or when filtering out data. There are quite a few scenarios where this function can be useful. For instance, creation of fields, testing values (to create flags) in a column and creating sub sets from existing data sets. Check out the video demo below:

Give it a go and share your experiences. Please sign up for our newsletter, so that we may keep you posted on the latest activity on our website and Youtube channel.

 




Audit Command Language – Append




Hi dear readers. Welcome to another post in the series ‘Audit Command Language Tutorial for Beginners’. We have been making reasonable progress on this series. We certainly hope all our readers have been finding the posts useful. We have been getting some comments to that effect and it is encouraging to get some feedback from readers. So please keep the input coming and we will try to improve as much and as quickly we can.

In this post we will be discussing an important concept. till now, we have only discussed techniques or concepts with which to manipulate data files within a single data set i.e. creating data fields or adding columns in a view and other things like. However, in a real world project situation, as handy as these basic tools are, we still need to understand how to play around with the whole data sets i.e. combine and multiple data sets and extracting relevant information from multiple data sets into a new data set. Some aspects Audit Command Language scripting are designed to exactly to facilitate these tasks. so with that, we will be covering the Append  statement in this post.

As the name suggests, the Append statement would allow us to append something. But what? how? etc. These should be questions that should come to your mind now. The Append statement allows us to vertically combine to two or more data set in the Audit Command Language. Just like all aspects of the ACL Audit Command Language, this can be achieved in the 2 ways i.e. via GUI or by audit command language scripts.

Before we discuss how to use the Append statement, we must know the requirements for this statement to work correctly. In order to use this statement, the data sets to be combined should be identical wit regards to the the number of fields, the data types of the fields and the fields lengths. If these criteria are not met, the results from this statement would be incorrect. The ACL tool would probably not even provide an error warning. Needless to say, these conditions should be checked before using the append command.

Check out the video demonstrating the Append Statement below:

The video gives a detailed description of how to use the Append statement. Listed below are the ways to understand the scripts used to achieve the same for the readers reference.

OPEN PAYROLL
EXTRACT FIELDS ALL TO TEMP IF RECNO < 10q

OPEN PAYROLL
EXTRACT FIELDS ALL TO TEMP IF RECNO >20 Append

The above scripts are simply creating a new table from the table ‘Payroll’ where the new table should only include the first 9 records and all records after the 20th records in the data set ‘Payroll’.

It is a simple enough statement to implement in your projects. We hope this post was helpful. Please go through the website to review ACL script examples and ACL script commands sign up for our newsletter, so that we may keep you posted on the latest activity on our website and Youtube channel.

 




Audit Command Language – Classify Command




Hello folks. We hope that you have been following along and more importantly this information has been helpful to you in some way. As always we are trying to add as much content on this site as regularly as possible so that readers can easily follow along and keep up the enthusiasm for learning.

In that spirit, we will keep on going further with another post in the ‘Audit Command Language Tutorial for Beginners’ series. In this post we will be discussing the Classify command. The Classify command is among one of the most frequently used commands in most ACL projects. The Classify command simply displays all the unique values along with the count of total occurrences of each of the unique values. This result is mostly show on the screen and/or it can be printed in the logs as well.

The Classify command clearly bares a lot of similarity to the the summarize command. The major difference between the two is that, when a data set is summarized, we are not sorting the data file and no additional fields besides the key field (the field on which data is classified) and any subtotal fields are allowed. Also, the  Classify command is faster than a summarize if we simply want to extract the unique values because it doesn’t bother with presorting the data, which is a requriement for summarizing any data set.

Let us now explore how to actually use the command. As all things in Audit Command Language, this operation can also be performed using the GUI of the tool and via scripts. to leverage the GUI open the table and simply go to the menu, choose ‘Analyze’ -> ‘Classify…’. A pop window should pop up like shown in the screenshots below:

In the above screenshot, we can select the one field on which we wish to classify the data, the subtotal fields and the any ‘IF’ conditions that we may want to apply in order to filter out the data.

If we move towards the ‘Output’ tab, we get the options to choose the output format i.e. whether the output from the classify command would be provided on screen or in a new table. Depending on the selections that a user would make, we get a small changes to the scripts. For the above set up the Audit Command Language Script would come out as:

OPEN PAYROLL
CLASSIFY ON WorkDept SUBTOTAL Gross_Pay TO “test.FIL” OPEN

For more detailed explanation, we urge you to view the video demonstration of the Classify command. See below:

Since this command is so useful, it is important to understand clearly how it works by playing around the GUI and figuring out changes in output and the scripts created in the logs. This is a terrific way of understanding the Audit Command Language Scripts syntax. Try out this command and share your views in the comments.

Please keep practicing and feel free to reach out to us with your valuable feedback and comments. Please go through the website to review ACL script examples and ACL script commands sign up for our newsletter, so that we may keep you posted on the latest activity on our website and Youtube channel.