Let's be honest: if you work with data in any capacity, you've found data quality errors before. You've seen a variable that uses several different formats, a row that is fully uppercased when others are proper cased, or a proper noun that is spelled fifteen different ways throughout the data set. Identifying the issues is half of the battle - but what do you do now?   In my last post, I demonstrated how to use the Clean Data step in SAS Studio to analyze your data and quickly uncover data quality issues. This time, I’ll show you how to use the exact same step to effortlessly clean up your data in a few clicks with the casing and standardization operations.   Click Read more to continue our data quality journey with SAS Studio Flows!   The Clean Data Step (Review) and Scenario   Recall that the Clean Data step (available with the SAS Studio Engineer license) enables five operations based on the SAS Quality Knowledge Base (QKB): standardization, casing, gender analysis, identification analysis, and pattern analysis. Visit the documentation for a full description of step capabilities.   Last time, I used the analysis operations to quickly identify issues and summarize data in the CONTACTS table from the SASDQREF library.   Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   The findings include:   Casing is inconsistent in several columns (COMPANY, CONTACT, ADDRESS, STATE) COMPANY names are written with many variations STATE values switch between full names and abbreviations PHONE values use the same standard pattern except for one single record ADDRESS values lack a standard for expressing road names (i.e. Street vs St) CITY lacks a standard for commonly abbreviated prefixes (i.e. South vs. S)   We’ll use the standardization and casing definitions to clean up these issues.   Casing   Case definitions convert text to uppercase, lowercase, or proper case. This may sound like an overly simple set of transformations. However, the QKB also includes specific proper case definitions for various data types including names, organizations, or addresses. In these cases, special capitalization rules may apply to certain values.   For example, proper casing the value BEN MCDONALD would return Ben Mcdonald, but applying the Proper (Name) definition would return Ben McDonald, which is standard for names with a Mc- prefix.   Review some casing examples below:     For more information on casing, visit the QKB documentation.   I’ll use the Proper (Name) case definition on CONTACT values. Some names are uppercased, which I want to correct.     Simply connect a Clean Data step to CONTACTS in your flow, select the ENUSA locale, and enable casing. Then, select the CONTACT column and the Proper (Name) case definition. I’ll create a new column to store my results.     The default output column name will be InputColumnName_CASE.   The results show that CONTACT names have been appropriately cased.     Notice that names with special casing rules (like Mc- prefixes) are also appropriately cased.     We’ll correct the remaining issues using standardization definitions.   Standardization   Standardization definitions apply QKB rules and logic to the individual tokens in an input text string.   For example, say I want to apply the Address standardization definition to the street address 100 SAS CAMPUS DR. The value would be split into the street number (100), street name (SAS CAMPUS), and street type (DR). Then, the tokens are transformed as necessary: the street name becomes SAS Campus, and the street type becomes Dr. The tokens are combined again to make the standardized value 100 SAS Campus Dr.     Standardization definitions are available for other data quality tasks, including masking data or removing specific characters. For more information on standardization, visit the QKB documentation.   I’ll update my flow to enable standardization, then start with applying the State/Province (Abbreviation) definition to STATE.     Under the Additional Standardize heading, I’ll configure settings for my other columns as follows:   PHONE variable --> Phone definition ADDRESS variable -> Address definition CITY variable -> City definition COMPANY variable -> Organization definition   After running this step, I can compare the original columns to their new standardized output in the table viewer.     The STATE values now appear only as two-letter abbreviations, which is excellent compared to the wide variety of patterns found in my previous post. PHONE values are also uniformly standardized - notice that the Phone standardization definition uses a different format than the original column did.     ADDRESS and CITY have also been standardized nicely. The Address definition shortens directional prefixes (like East, West, North, and South) and road types (like Street, Road, Circle, or Avenue). The City definition does the opposite by spelling out directional prefixes in CITY names.     However, the Organization definition didn’t quite work for the COMPANY values. While row values now fit typical company name standards, we can still see several repeat values.   This issue can be solved in a few ways. If you don’t mind coding, check out my series on creating and applying custom schemes with PROC DQSCHEME. You can also check out the user-friendly point-and-click SAS QKB Definition Editor. In the Definition Editor, you can customize the Organization definition to include these values or create a new custom scheme or definition (without any coding). Customized QKBs can be saved and used in SAS Viya. To learn more, check out the workshops Understanding the SAS Quality Knowledge Base and Creating a New Data Type in the SAS Quality Knowledge Base.   Summary   In this post, I’ve demonstrated how to fix data quality problems with the Clean Data step. If you missed my previous post, give it a read to learn how this step can help you uncover your DQ issues. In the next part, I’ll show you how to retrieve specific tokens from your text data using the Parse Data step.   What semantic type of data do you spend the most time cleaning, if any? Does the Clean Data step help to accelerate your data preparation processes? Share your thoughts, questions, and feedback below!     Find more articles from SAS Global Enablement and Learning here. ... View more

Big data sometimes leads to big messes. What can you do when qualitative values in your data are inconsistently formatted, incorrectly parsed, or needlessly repeated? Furthermore, how can you identify these issues to begin with?   The Clean Data step is a multi-faceted point-and-click tool for data analysis and data cleaning in SAS Studio. In this post, I’ll demonstrate how to profile your data with the analysis features. If you’re interested in exploring data quality features in SAS Viya or you’re looking for an alternative to SAS Data Studio, click Read more to follow along!   The Clean Data Step and the QKB   The SAS Quality Knowledge Base (QKB) is a collection of files that store rules, logic, and reference data which are used to perform data quality operations. The Clean Data step is one of several flow steps that enables users to take advantage of the QKB.   This step enables five operation types: standardization, casing, gender analysis, identification analysis, and pattern analysis. In this post, I’ll use the three analysis operations to profile my sample data. For reference, all five operations can be performed up to 10 times each in one Clean Data node. Visit the documentation for more information on step capabilities.   Scenario   In this series, I’ll be using CONTACTS, which is a sample table in the SASDQREF library. CONTACTS contains contact information like name, company, address, phone, and more.   Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   Observing the first few rows reveals that the data is imperfect. What issues do you notice? Some problems include:   Casing is inconsistent in several columns Company names are written with many variations State values switch between full names and abbreviations   We'll use the various analysis definitions to get a clear picture on the quality of this data.   Pattern Analysis   Pattern analysis identifies patterns in character strings by substituting text with pattern symbols. Patterns can be identified either by every character or every word (where "word" means grouping of the same type of character) in the string. For example, a product code BCD-1234 would have the character pattern AAA*9999 and the word pattern A*9.   This can be useful to quickly profile columns where we’d expect values to follow a distinct pattern, like phone numbers. Phone numbers in the United States are ten digits long and commonly stored as two sets of three digits and one set of four digits, with each set being separated by a symbol. If the column values are inconsistently formatted, we can identify the most common pattern to use for standardization.   For more information on pattern analysis, visit the QKB documentation.   I’ll analyze patterns in STATE and PHONE from CONTACTS.     STATE appears to be lacking a standard – some state names are fully spelled out, while others appear in two-letter abbreviations. PHONE appears to follow the expected standard, but I’d like to check all values in the column.   In my flow, I’ve connected a Clean Data step to CONTACTS, selected the ENUSA locale, and enabled pattern analysis. I’ll analyze STATE using the Character definition, so I can identify the distinct abbreviation formats (or lack thereof). Then, I’ll analyze PHONE with the Word definition, so I can see if the values appear as three groups of numbers separated by two symbols.     Note that the default output column names will be InputColumnName_PATTERN.   I’ll also add a Characterize Data step to quickly summarize the output columns. To learn about the Characterize Data step, visit the documentation.     The output shows that 96% of STATE values appear as two-letter abbreviations, but the remaining values vary greatly in representation.     PHONE values appear much more consistent – all but one value fits the expected standard.     Identification Analysis   Identification analysis is used to identify or validate the semantic type of information stored in a string. For example, the string 10/18/2007 could be identified as a DATE, or it could be recognized as a VALID date in MDY format.   For more information on identification analysis, visit the QKB documentation.   I’ll analyze CONTACT column values. Currently, I'm expecting all values to represent individual people but there are visible inconsistencies in the format of names, which makes me want to validate this column. Additionally, some records may list companies, department names, or job titles as contacts.     I’ll update the flow to add identification analysis on the CONTACT column, using the Field Content definition.     After updating the Characterize Data step to include the new output variable, we see that most names were identified as INDIVIDUAL, but some received other output.     I’ll investigate further by adding a Query node and filtering to retrieve all rows where CONTACT_ID was NOT equal to INDIVIDUAL.     The results show that these CONTACT values contained elements like uncommon names, last names that sound like road names, or non-letter symbols. These discrepancies could be corrected by customizing the QKB to account for uncommon names and processing this column to remove symbols.   Gender Analysis   Now that we’ve determined that all CONTACT values appear to represent individuals, we can perform gender analysis on this column. Gender analysis uses algorithms to identify an individual’s gender based on their full name. These algorithms consider historically gendered names, titles, and suffixes. For example, Mrs. is a common female title, while Jr. is a common male suffix.   For more information on gender analysis, visit the QKB documentation.     I’ll update my flow to add gender analysis on the CONTACT column. Notice that there’s only one valid definition for gender analysis – Name.     After adding CONTACT_GNDR to the Characterize Data step, the results show that the percentage of F and M results were close. However, roughly 20% of the names were listed as U for unknown. Like I did previously, I’ll run a query to filter only for the unknown output.     The results simply show that many of these names did not have enough information to identify gender. Most CONTACT values with unknown gender use a first initial instead of a full first name. Others use names which are not recognized as gendered by the QKB.   Summary   In this post, I’ve demonstrated part of the power of the Clean Data step and the QKB by using analysis definitions to profile the CONTACTS data set. In the next part, I’ll discuss how to use the rest of this step to fix some of these issues we’ve identified by standardizing and casing. Interested in more content on data quality? Check out these posts on the QKB: What is a QKB?  SAS Quality Knowledge Base (QKB) Customization Tools – Available for All SAS QKB Customers SAS Viya: Add a Customized Quality Knowledge Base (QKB) to CAS and SAS Compute   Which analysis definitions would you find the most useful for your data preparation process? Share your thoughts, questions, and feedback below!       Find more articles from SAS Global Enablement and Learning here. ... View more

Lookup tables are a common efficiency tool. When a process produces hordes of data, you can preserve memory and improve performance by storing that information in multiple tables (such as customer data and order data). On the other hand, your data may include one or more codes from a common reference table, like a set of medical billing codes and their meanings. As the name suggests, lookup tables are used to perform table lookups when you want to add reference data to a table.   If you’re a low-code/no-code SAS user, many methods are available for performing table lookups. In this post, I’ll use a variety of tables to demonstrate four table lookup methods available in SAS Studio Flows.   Scenario   Through this post, I’ll be using one main table and seven different lookup tables. Observe the table relationships below:   Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   The main table, CUSTOMER, contains a list of all customers and their demographics (totaling 100,004 rows).   Three of the lookup tables (TRANSACTION, MERCHANT, and BANK) store purchase related information. I’ll perform table lookups to enhance the customer transaction records with information like where the transaction occurred and which bank approved the transaction.   The remaining four lookup tables (STATEPOPULATION, DIVISIONCODE, REGIONCODE, and STATECODE) store geographic information. The set of US population data uses abbreviations and codes to refer to the state, region, and division in each row. I’ll perform table lookups to replace these codes with descriptive labels.   Both result sets will be used to perform a final lookup with the customer data.   Lookup Custom Step   First, I’ll simply add the bank name to each transaction based on the listed bank ID number.     The Lookup custom step is perfect for this task, because it is easy to configure and returns one column from the lookup table. This step only requires you to select the columns to keep from the main table, the lookup key columns, and the return column from the lookup table. When submitted, this step will use hash objects to perform the lookup. However, note that custom steps are created and maintained by SAS users and are not available out-of-the-box. Visit the custom step's README documentation for a full description of step capabilities, and click here to learn how to use custom steps from the SAS Studio Custom Step GitHub Repository.     I’ll keep all columns from the main table, use BankID as the key column, and return the Name column.     The results show that the bank name was successfully added for each transaction with an associated BankID.   Query Step   Next, I’ll use the Query step to add merchant data to each transaction as well.     The Query step is multifaceted and can be used for tasks like sorting, deduplicating, and aggregating data. Another primary feature of this step is performing joins, which are another table lookup method. First, configure two or more input ports on the Query node. Then, you can specify join types and conditions on the Join tab. When submitted, this step will run a PROC SQL (or PROC FEDSQL) query. Visit the documentation for a full description of step capabilities.     I want to join the TRANSACTION_PARTIAL results with MERCHANT in a left join where MerchantID is the same in both tables. In addition, I’ve selected all columns from both tables for output (excluding duplicates).     The results show that descriptive merchant information has been added for each transaction, including the merchant’s name, type, and provided service. All the transaction data has been successfully combined into one output table, TRANSACTION_FULL.   Calculate Columns Step (with custom formats)   Now, I’ll switch to working on my state population data. I’ll create formats based on the STATECODE, REGIONCODE, and DIVISIONCODE tables, then apply those formats to STATEPOPULATION using the Calculate Columns step.     Each table is a simple lookup table containing a code and its label. For example, the StateCode NC has the label North Carolina, or the RegionCode 3 has the label South Region. I’ll convert the tables to formats with the following code, which can be executed with a SAS Program step.   proc sql; create view region_fmt as select "$region" as FMTNAME, RegionCode as Start, RegionName as Label from regioncode; quit; proc format cntlin=region_fmt; run; proc sql; create view division_fmt as select "$division" as FMTNAME, DivisionCode as Start, DivisionName as Label from divisioncode; quit; proc format cntlin=division_fmt; run; proc sql; create view state_fmt as select "$state" as FMTNAME, StateCode as Start, StateName as Label from statecode; quit; proc format cntlin=state_fmt; run;   Once the formats have been created, they can be applied with the Calculate Columns step. This step has an expression builder which can use any valid DATA step expression to create new columns. When submitted, this step will run DATA step code. Visit the documentation for a full description of step capabilities.   For this method, I’ll use the PUT function to apply the three new formats based on their original code columns, like so:   PUT(Region,$region.)     The results show that STATEPOPULATION has been enhanced with descriptive RegionName, DivisionName, and StateName for each state entry.   Lookup Step   Finally, I’ll perform table lookups to add TRANSACTION_FULL and STATES_FULL to the CUSTOMER table with the pre-built Lookup step.     The Lookup step became available starting in LTS 2024.09, making it the newest SAS Studio Engineer step. With this step, you can input one or more lookup tables, and you can control settings and features like key columns, filters, and exceptions for each lookup table. Optional error tables can be included in the results if you wish to analyze any rows that were not included in a successful table lookup. Visit the documentation for a full description of step capabilities.     After connecting TRANSACTION_FULL and STATES_FULL as lookup tables, I’ll select the key columns for each: TRANSACTION_FULL will use AccountID, and STATES_FULL will use Name (which matches with CUSTOMER.State).   In addition, I’ll filter TRANSACTION_FULL. I know that not every customer will have an AccountID. In addition, not every transaction has an associated AccountID. However, I don’t want to create a false join based on missing AccountID values, so I’ll add the filter:   AccountID is not null   Note that filters need to be written in SQL WHERE clause syntax, but must exclude the initial WHERE keyword.     Exceptions can be set to handle any possible errors during processing. Three exception conditions are possible: Lookup table missing, Lookup table contains no records, or Lookup value not found. An associated action can be set to execute when exceptions occur during processing. By default, when the lookup table is missing or the lookup table contains no records, the step will Abort process. I’ve added some processing for when the lookup value is not found. The main table’s row will be added to the error table and to the exception table, and the target column will be set to missing in the output table. However, you could choose other actions such as skipping the row or setting the column to a specific value.     Setting exceptions correlates directly to configuring error tables on the Error Tables tab. These are enabled by checking the box to create the table. An error table will simply contain the source table row that triggered the error. You can select one or more columns from the source table to include (I selected all columns) and you can also choose to limit the number of errors to store. If selected, this will surface additional options for error limits.     An exception table may seem similar, but it has different features. By default, it will include three columns containing the lookup table name, the exception condition that was triggered, and the exception action taken. You can choose to add target table columns here to help identify the record associated with the triggered exception. I’ve added identifying information, like customer AccountID, BankID, FirstName, and LastName.     The results contain a mega-table lookup result. This data contains all information regarding each customer, any associated transactions, and state demographics. This makes for a total of 106,680 columns and 54 rows. This result can be queried further to answer questions like: How many customers made more than one purchase, and what are their associated incomes? Did a merchant have any transactions from customers who were out-of-state? What percentage of the total recorded spending comes from customers in each state, region, and division?     Our error tables are included in the output as well. This preview of the EXCEPTIONS table shows that many customers appeared not to have an AccountID, meaning that they could not be joined with any transaction data. Based on my settings, the error table will have the same number of rows and can be used to answer other questions about the data.   Considerations & Summary   Each of these lookup steps use different underlying methods and vary in features, including those to control output and results. The best step for your needs will depend on how much control you want over your lookup settings and results.   These steps also vary based on availability by license. Find information on SAS Studio licensing and included steps here.   In this post, I demonstrated how to use four different methods to perform table lookups: the Lookup custom step, the Query step, custom formats and the Calculate Columns step, and the SAS Studio Engineer Lookup Step.   Want to know more about data manipulation in SAS Studio? Check out my previous blogs in this series on appending, sorting, deduplicating, and aggregating data.   Do you need to perform table lookups often? Did you learn a new lookup method today? Let me know!     Find more articles from SAS Global Enablement and Learning here. ... View more

Aggregating your quantitative data is a simple way to summarize and present the story your data is telling. Do the values fall in a large range, or a small one? What's the average value, and how does it compare to the median?   With SAS Studio Flows, you can aggregate your data with multiple methods which are friendly to both point-and-click users and customization-loving users.   In this post, I’ll use the SASHELP.CARS data set to show you the aggregation capabilities of four steps: Summary Statistics, Query, Manage Columns, and Create Columns.   Summary Statistics step   The Summary Statistics step is designed for the SAS user that wants to summarize data with the click of a button. By default, this step will generate the mean, standard deviation, minimum, maximum, and count of non-missing values for selected numeric variables. You can enhance your results with options like grouping statistics by a qualitative variable, adding a weight variable, selecting additional statistics, and visualizing your results. Visit the documentation for a full description of step capabilities.   Through this post, I’ll be working with the SASHELP.CARS data set.   Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   After connecting your source data to the Summary Statistics node, select one or more analysis variables. I’d like to generate statistics for MSRP and Invoice.     All statistical analysis options appear on the Options tab. By default, all basic statistics (except the number of missing values) are selected. The maximum decimal for each statistic will be the best fit and the divisor for standard deviation and variance is degrees of freedom. Options like additional statistics, percentiles, and plots are available under their respective headings.     At times, selecting one of these options will surface additional options. For example, selecting the option to generate a histogram will surface additional plot options.     After running the step, the summary statistics are displayed in a report with plots beneath them. You can choose to save results in a table instead on the node’s Output tab.     I can further augment my results by adding a classification variable, Make. This will calculate summary statistics for each unique vehicle make included in the source data. I’ll combine classification variables by n-way only, meaning that my results will include only the grouped statistics and not the statistics summarizing the entire table.     The results will be split into separate rows for each classification variable value.     Query step   The Query step has appeared in my last two posts on sorting and deduplicating data. Yet another capability of the Query step is aggregating data. You can generate statistics by calculating new columns with statistical functions. Visit the documentation for a full description of step capabilities.   After connecting source data to the node, the columns in your data will appear on the Options tab. Above that list is the option to calculate a new column.     Selecting this option will surface the Expression Builder. Here, you can manually type your calculation or build it by selecting data and functions from the left menu. Column attributes can be added at the bottom of the window. In this case, I’d like to calculate the mean of the MPG_City column.     As columns are created, they will appear in the Query node’s selected column list. I’ll create two more columns: one to calculate the mean of the MPG_Highway column, then one to calculate the mean of Avg_MPG_City and Avg_MPG_Highway.   When any column is selected in the column list, the option to Convert to Aggregate becomes available. Clicking this option allows you to select from a list of summary functions to apply to the selected column, which further simplifies the aggregation process.     Running this step selected yields the results below: three average MPG values.     What if I want to calculate these values for each distinct vehicle Make, like I did in the Summary Statistics step?   I’d simply add Make to my column list, then add Make on the Groups variable tab as well.     The results are now grouped by distinct Make values.     Manage Columns Step   The Manage Columns step is a simpler step with more focused capabilities. Manage Columns is used for tasks like selecting columns for output, changing column attributes, reordering columns, and calculating new columns. Visit the documentation for a full description of step capabilities.   Much like the Query step, the Manage Columns step has an option to create a new column which will surface the same Expression Builder interface shown previously.     With this, I can create output identical to my initial Query step results.     However, I can't go further here and calculate statistics for the distinct values of a grouping variable. You can only calculate full column aggregations with Manage Columns.   Calculate Columns Step   The Calculate Columns step is used to create new columns based on existing data. Though it sounds like the previous two methods, there’s a major difference. While Query and Manage Columns calculate statistics down a column, Calculate Columns can only calculate statistics across a row. Visit the documentation for a full description of step capabilities.   The Calculate Columns step enables you to transform existing columns or create new ones. Transformation options include type conversion, casing, value extraction, and formatting.     Again, the option to create an entirely new column surfaces the Expression Builder. This time, I’ll create a new column named Profit which subtracts the Invoice from the MSRP for each row.     Running this step shows that a Profit value was calculated for each row.     Considerations & Summary   When choosing which step to use for aggregating data, several factors come into consideration. While the Query and Manage Columns steps are available with SAS Studio Basic, the Summary Statistics and Calculate Columns steps require a SAS Studio Analyst license (or higher). Find information on SAS Studio licensing here.   The Summary Statistics step makes it easy to generate desired statistics and create attractive reports. The Query and Manage Column steps allow you to have maximum control the statistics you calculate and how they're calculate, though the Query step has the additional capability of grouping results. The Calculate Columns step offers a unique output from the other steps, as it enables aggregating across a row instead of down a column.   In this post, I’ve discussed four methods for aggregating data in SAS Studio Flows. Want to know more about data manipulation in SAS Studio? Check out my previous blogs in this series on appending, sorting, and deduplicating data.     Find more articles from SAS Global Enablement and Learning here. ... View more

Have you ever needed to cut down a large data set, but aren’t sure how? Chances are you’ll need to remove duplicate rows or values in your data, whether that’s to eliminate redundancy or to simply analyze rows with unique field values. Much like other common data manipulation tasks, SAS Studio Flows has multiple steps that can remove duplicates in a few mouse clicks!   In this post, I'll show you three different steps which can perform three different types of deduplication: Remove Duplicates, Sort, and Query.   Remove Duplicates step   As the name suggests, the Remove Duplicates step will remove duplicate rows from your data. While other steps have some flexibility regarding how duplicates are removed, this step will only remove complete duplicate rows. In other words, this step eliminates rows that match the values of another row for every field. Visit the documentation for a full description of step capabilities.   Say you’re working with an augmented version of the SASHELP.CLASS table, CLASSDUPS. This table includes duplicates of some student records.   Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   To use the Remove Duplicates step, simply connect your source data to the node. The only required setting is a simple check box to remove duplicates across all columns.     If desired, you can adjust output settings. By default, the step will replace the existing output table with the same name and write duplicate rows to the log, but you can choose to store duplicates in a separate table instead.     After running the step, all duplicate rows are removed.     Duplicates are also written to the log due to the default output settings.     Sort step   The Sort step was the main subject of my last post. Though its primary capability is to sort data, this step can also be used to remove duplicate rows. Additionally, this step allows more control over how to handle duplicates – you can choose to remove duplicate rows based only on the sorting columns, or by all columns. Visit the documentation for a full description of step capabilities.   Say you’re analyzing the SASHELP.FISH table, which contains measurements of fish caught in Lake Laengelmaevesi. Measurements include weight, three different lengths (nose to beginning of tail, nose to notch of tail, and nose to end of tail), height, and width.     You’d like to quickly extract the first appearance of each species in the data. This data has been pre-sorted to list each specimen by increasing length within each species, meaning that the first occurrence of each species in the data will have the shortest length measurement.   First, configure the step to sort the source data by Species in ascending order.     Notice the Options button on the tab’s toolbar. If selected, you can configure duplicate row behavior with three options: Keep all rows, Remove rows with duplicate values in sort columns, or Remove duplicate rows. The final option would yield results identical to the Remove Duplicates step. For the desired result, select Remove rows with duplicate values in sort columns.     Once you run this step, the output data will show only one row for each species, since all duplicate values in the species column were removed. The remaining entries represent the shortest specimens in each species.     Query step   The Query step is powerful and multi-faceted, capable of performing many different actions on your data. Not only can it sort data, but it can remove duplicates with flexibility like the Sort step by using the option to select distinct rows only for the results. Visit the documentation for a full description of step capabilities.   Say you’re analyzing SASHELP.ORSALES, a table containing data about the fictional sports store Orion Star.     ORSALES has 912 rows, but we can use the Query step to quickly find all of the product lines and categories that Orion Star carries. After adding and connecting the appropriate nodes, start by selecting Product_Line and Product_Category for the query output.     Then, navigate to the column options > More options and select Select distinct rows only. With this option toggled, the output will contain only the distinct combinations of unique Product_Line and Product_Category values from the source data.     The output has 12 rows. These represent four product lines: Children, Clothes & Shoes, Outdoors, and Sports. Most lines only have one or two product categories except for the Sports line, which has eight categories.     Notice that this deduplication method differs from the Sort step method. The Sort step enabled deduplication of sort columns only, but the output contained all source columns. Alternatively, the Query step enabled deduplication of the selected columns, but the output contained only the selected columns. The option to select distinct rows will apply to every selected column. Therefore, if all columns are selected for output, the distinct rows option will behave like the Remove Duplicates step.   Considerations & Summary   These deduplication options are available with every SAS Studio license (much like the sorting options shown in my previous post).   As I’ve demonstrated, each step mentioned in this post has slightly different capabilities. You’ll need to choose your deduplication step based on exactly which fields you want to remove duplicates from and which fields you want to include in the output.   In this post, I’ve discussed three methods for deduplicating data in SAS Studio Flows. Want to know more about working with data in Flows? Check out my previous posts in this series on appending and sorting data. Look out for future posts on aggregating data and performing table lookups!   Which of these deduplication methods is most useful for your work? Will you use these methods for improving data quality, exploring data, or both? Let me know!     Find more articles from SAS Global Enablement and Learning here. ... View more

Sorting data is perhaps the most common data manipulation task. Your data may need to be ordered from newest to oldest observation or largest to smallest measurement. You might need to list all of a store’s departments in alphabetical order, then list each department’s products by lowest to highest MSRP. You may simply be preparing your data for another procedure that requires your observations to be sorted.   Regardless of your needs, you can fulfill them quickly and easily with SAS Studio Flows. Each method I’ll show today is available with a SAS Studio Basic license, which is a testament to how common this task is.   I’ll demonstrate the sorting capabilities today with the SASHELP.Springs dataset, which stores information about hot spring locations in the United States.     Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   I’ve used the scatter map step to briefly visualize the locations represented in this data.     Every location is in the United States, which is north of the Equator and west of the Prime Meridian.   Sort step   The Sort step enables you to sort your observations by any selected variables. You have the freedom to sort by all variables or only one, in addition to selecting the sort order (ascending or descending) for each variable. This step generates PROC SORT code to store sorted results in an output table.     The step options are straightforward. As you select columns, you can select your sort order. Additional options are available for handling behavior like removing duplicates. For more information, visit the step documentation.   In this example, I’d like to sort by descending temperature so that my observations are ordered from hottest to coolest spring. I’ll use Fahrenheit, but Celsius is also available in this data.     The results show the hottest spring at the top (Makushin Volcano Fumaroles in Unalaska).     However, some locations share the same temperature. I’d like to go back and do some additional sorting to see the southernmost location first.     The southernmost location will have the shortest latitude, and latitude will be a positive number since our locations are north of the Equator. Therefore, I can add ascending latitude as my second sort column.     Notice that now, in addition to sorting by temperature, locations with the same temperature are sorted by latitude. As you may have suspected, these locations are very close in latitude. I’ve used the scatter map step once again to show these results.     Query step   The Query step enables you to build a custom SQL query. With this step, you can utilize every optional SQL query clause (WHERE, GROUP BY, HAVING, ORDER BY) and common SQL tasks like joining tables or calculating new columns. Options for different clauses and features are hosted in separate sub-tabs. Running this step will generate PROC SQL code to store results in an output table. For more information, view the step documentation.     Because this step builds a query, I’ll need to choose my output columns in the Select tab first before I can consider sorting. I’ll add all columns for the purpose of this demo. On the Sort tab, I can select my sorting columns. The interface is similar to the Sort step. This time, I’ll sort by descending temperature in Celsius.     I can set additional query options if needed, but that isn’t necessary for my case.   My results are very similar since I am simply using a different measurement of the same temperature. However, there are some edge cases where two different temperatures in Fahrenheit are equivalent to the same temperature in Celsius. In this case, 208 degrees and 209 degrees Fahrenheit both equal 98 degrees Celsius.     I’d like to add another sort variable again. This time, I want to see the easternmost location first for locations with the same temperature. Because our locations are all west of the Prime Meridian, longitude is expressed as a negative value - meaning we’ll need to sort by descending longitude.     This time, longitude varies a little more for locations with Celsius 98.     Considerations & Summary   Unlike other data manipulation tasks, you do not need to consider your SAS Studio license to select your sort methodology. Both the Sort and Query steps are available with every SAS Studio license!   Whichever step you use will depend on your needs. If you simply need to sort your data, the Sort step is perfect. However, if you need to complete a few different tasks like calculating columns, filtering rows, and sorting, the Query step might work better since it performs all of these operations (and more).   In this post, I’ve discussed two short-and-sweet methods for sorting your data in SAS Studio Flows. If you’re interested in other data manipulation tasks, check out my previous post on appending data and stay tuned for upcoming posts! How often do you need to sort your data? Are there other data manipulation tasks you’d like to hear about? Let me know!     Find more articles from SAS Global Enablement and Learning here. ... View more

SAS Studio Flow functionality has been booming over the last several months. In the last year, dozens of steps have been added and even more have been updated! That means now is better than ever to learn some SAS Studio Flow basics. I’ll be posting a series of blogs covering how to do common data management tasks in SAS Studio Flows, starting with appending data.   Insert Rows step   The Insert Rows step uses a SQL INSERT statement to add rows to a table from a generated query. Use of this step requires a SAS Studio Analyst license.   This step allows you to append rows from the input table to existing rows in the output table, replace the output table data with the new input table data, or populate a new table with rows from the input table. Read the documentation for a full description of step capabilities.    Simply connect your source table to the input port, connect your target table to the output port, check your column resolution, and review the output table options. The options include creating a table if physical table does not exist and deleting all existing rows in the output table.   For Insert Rows to run successfully, source and target tables must have at least one mutual column (column with the same name and same type). If not, you can edit your target table’s column structure on the table node’s Published columns tab or by adding a column editing node to your flow.   Let’s review an example. I have a target table, PAYROLL, with pay information for fictitious employees. I’d like to append some new rows from PAYROLL2 to this table.   Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   When I connect my source and target tables, there’s an info symbol on the Insert Rows node. This tells me there’s an issue with the column resolution between these tables.     PAYROLL has an IdNumber column, while PAYROLL2 has an idnum column. I’ll correct this by adding a Manage Columns step and renaming idnum to match.     Now, the column resolution has been resolved and I can run this flow with no issues. The log shows that a SQL INSERT statement was submitted to append this data.     Load Table step   The Load Table step can insert, update, or upsert rows from a source table to a target table. Not only does this step boast update capabilities, but the user can select their preferred insert method: PROC SQL INSERT statement or PROC APPEND. (Note that this feature became available in the 2023.09 Viya stable release). Use of this step requires a SAS Studio Engineer license.   This step performs three primary functions. First, you can insert new source table rows into the target table, with preprocessing action options to control what happens to existing target table rows. Second, you can update existing rows in your target table based on matching key column values (like a product’s price or an employee’s salary). Lastly, you can do inserts and updates at once with the “upsert” option. Read the documentation for a full description of step capabilities.    The Load Table step configuration is slightly different from Insert Rows. Connect your source table to the input port and select your target table on the Target Table node tab. Then, configure your insert or update settings on the options tab. If you’re creating a new table, you can define the table metadata within the Load Table step. Otherwise, you can review source and target column mapping on the Column Resolution tab.   Another plus is that the Load Table step supports bulk loading for certain database tables, which promotes efficiency.   Let’s review an example working with the same tables, PAYROLL and PAYROLL2.     PAYROLL2 and the Manage Columns node are connected to the Load Table input port. PAYROLL is selected as the target table.   All table load options are set on the options tab. First, I want to insert rows with PROC SQL, but note that I could do an update or upsert as well.     This step and setting produced code similar to our previous results from the Insert Rows step.     I can also change the insert method to PROC APPEND. I’ll force table concatenation for non-matching columns and suppress warnings in the log.     Now, the generated and submitted code has changed to use PROC APPEND.     Merge Table step   The Merge Table step uses a SQL MERGE statement to insert, update, or upsert rows from a source table to a target table. This functionality is very similar to the Load Table step, but it’s limited to certain databases that allow SQL merges (Oracle, Teradata, Snowflake, SQL Server, etc). Since this step processes in-database, this makes for very efficient insert/update operations. This step also requires a SAS Studio Engineer License.   Read the documentation for a full description of step capabilities. For a full Merge Table step tutorial, check out my post Data Integration with the Merge Table step in SAS Studio Flows.   Append Table custom step   The Append Table custom step, available through the custom step repository, uses PROC APPEND to append new rows to a target table. Use of SAS custom steps in a flow requires a SAS Studio Analyst license.   For this step, connect your source table to the input port and the target table to the output port. If your target table does not exist, your source table will simply be copied as the target table. Like Load Table, you can choose to force append and suppress warnings in the log.     This step uses SAS macros, which can be reviewed in the log. The PROC APPEND operation can be reviewed here.       Considerations and Summary   Which tool should you use for your needs? Consider factors like your SAS Studio license, your data type, and the amount of rows you want to append. You can use the following table for a quick reference guide:     In this post, I’ve discussed four different point-and-click steps for appending data in SAS Studio Flows: Insert Rows, Load Table, Merge Table, and the Append Table custom step. Stay tuned for future posts on common data manipulation tasks in SAS Studio Flows!   Did you learn a new way to append data through this post? Let me know in the comments! ... View more

Last year, I posted a series on creating, applying, and converting standardization schemes using SAS Data Quality programming and the Quality Knowledge Base. If you’re familiar with SAS Data Quality tools, you’ve probably seen or used standardization definitions before. Every QKB locale includes a set of definitions for data cleansing tasks like standardization, entity resolution, casing, parsing, extraction, and more.   You may be wondering: what exactly are standardization schemes and standardization definitions? What’s the difference? Is there a difference? How do you use them? Is a scheme better than a definition (or vice versa)?   In this article, I’ll help you differentiate between schemes and definitions. As the name suggests, both tools are used for standardizing data. Though a scheme and a similar definition can produce the same results with the same input data, this is not always the case. The primary difference between schemes and definitions is that they’re performing different operations under the hood. Let’s take a closer look at both.   Standardization schemes   Standardization schemes are simple lookup tables used to standardize variations of the same value. A scheme contains a list of variations on data values and their associated standard value. When applied to a variable, a “find and replace” method is used to replace relevant values with the standard value.   It can be beneficial to create custom standardization schemes if your data includes unique variations or data types. In previous articles on PROC DQSCHEME, I created a scheme to standardize the appearance of car models and manufacturers in my data.   Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   Today, I’ll explore a different scenario. In this example, I want to establish a standard for various city names in North Carolina. My original data includes quite a bit of variation in casing and spelling.     I’ll use PROC DQSCHEME to create a scheme, CITY_SCHEME, based on this data.     proc dqscheme data=nc_cities noqkb; create matchdef='City' var=city scheme=city_scheme locale='ENUSA'; run;     Then, I’ll apply the new scheme using PROC DQSCHEME once more.     proc dqscheme data=nc_cities out=nc_cities_scheme noqkb;    apply scheme=city_scheme var=city; run;       My original data looks a lot neater now! However, be mindful that custom schemes will only transform values that appear in the scheme. Let’s say I have a similar set of data for South Carolina city names.     What happens if I try to use my CITY_SCHEME on this data?     My custom scheme doesn't work here. Since schemes use a “find and replace” method, I’d have to update my scheme to include these values.   Note that in this section I’ve discussed custom standardization schemes. The QKB also comes with a set of existing schemes that standardize specific tokens in a string. These schemes are the foundation for QKB definitions.   Standardization definitions   Standardization definitions are more complex than schemes. Instead of using a lookup table, a standardization definition uses a combination of data quality operations to transform data. Input values are first parsed into separate tokens. For example, a person's whole name would be parsed into first, middle, and last name, plus any prefixes or suffixes. Then, each token is transformed individually according to the definition rules. Often, a standardization definition will include a standardization scheme for each token in a definition. At the end, every token is concatenated back into one output string.   I’ll return to my city name example. What if I simply apply the ‘City’ standardization definition to the NC city variable?     data nc_cities_stnd;     set nc_cities;     length def_out $15;     def_out=dqstandardize(city, 'City', 'ENUSA');     drop state; run;     This works with my North Carolina data and gives a result identical to the previous. Now I’ll apply this definition to the SC city variable, which I could not successfully standardize before.     This time, I get my ideal result. The South Carolina city names were standardized without any updates needed.   I can play around with this definition and try to standardize a series of completely random cities as well.     The results aren’t perfect (I’d prefer city names to be spelled out instead of using acronyms) but it’s pretty good for an out-of-the-box definition.   Summary   I’ve demonstrated two different methods to standardize city names. In this example, I was able to produce similar results at least once with each methods. Despite this, it’s important to remember that standardization schemes and standardization definitions differ in methodology.   Standardization schemes are great for standardizing a specific data set, like I did with the NC city data, or standardizing values that don’t have out-of-the-box definitions, like I did with car data in my PROC DQSCHEME article. When you make a scheme, you build a lookup table that knows which values to replace and what standard value to replace them with. You can standardize values but you’re limited to only the values that are present in the scheme lookup table.   Standardization definitions are great for transforming input values by using DQ tools and definition rules. These rules can be applied to any input value, which makes standardization definitions flexible. However, you forfeit some control by using them. While there are a wide variety of standardization definitions available (see the English, United States definitions), they don’t cover every possible data type. These definitions aren’t perfect and can output a value that you don’t want depending on your standardization preference and the rules of the definition (which vary by type and locale). You can standardize any input value but you can’t control how the value is standardized, so you may have undesired output.   In this article, I aimed to demystify the difference between standardization schemes and standardization definitions. Though they sound similar, there’s quite a difference between these two tools! Visit the most recent QKB documentation to learn more about schemes and definitions. If you’re interested learning more about SAS Data Quality, check out my series on custom standardization schemes or my colleague’s series on coding for data quality in SAS Viya. ... View more

Have you ever needed to combine data from multiple sources, like a database and a spreadsheet? How about finding a subset of data from multiple data sets, like the customers that only shop in person but never online, or the customers that have purchased items from every department in the store at least once? What if you had a tool that could do both of those things at once?   As SAS Studio Flow capabilities continue to expand, so do your low code/no code options for data processing and consolidation! Combine your data in a few clicks with the Union Rows step, available starting in the Viya 2023.05 stable or LTS 2023.10 releases. This step enables you to combine data from multiple sources (when compatible) using seven different SQL set operators. Additional options allow you to customize the step based on your data needs.   A SAS Studio Analyst or SAS Studio Engineer license is required to use Union Rows.   Overview   The Union Rows step requires at least two input tables, but you can add more input ports as needed. This step is configured on the Options tab, where you select the desired set operator and column matching method. Columns can be matched by position or by name but be mindful that the column types should also align according to the column matching method.    Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   Then, for each source table, you can choose to include distinct rows only or filter rows with a where clause.     When running this step, SAS generates PROC SQL code that creates a table based on the set operator result, which combines multiple query results. Various keywords are added based on step options. ‘DISTINCT’ selects unique rows only, ‘ALL’ includes duplicate matching rows in the results, and ‘CORR’ matches columns based on name. WHERE clauses are added when applicable.   Demo: School rosters   For example, say I’m working with class roster data from a secondary school. I want to compare the number of students in honors classes against the number of students participating in school clubs. I have rosters for two honors classes (Algebra and Calculus) and three clubs (Art, Cross Country, and Zoology).   First, I’ll use two Union Rows steps to combine the honors rosters, then the club rosters. Both steps use the UNION operator and store the results in a new table.     Since I have three club rosters, I’ll add another input port to Union Rows. This means the step will perform two unions to combine all three tables.     Now, I can answer some questions I have about this data with additional Union Rows nodes.   For example, I can use the EXCEPT operator to find all the students who are in honors math but no clubs. Specifically, I'll use ALL_MATH_H as my first table, select the EXCEPT operator, then use ALL_CLUBS as my second table.     The result table has zero rows, so all honors math kids are also in clubs. This may suggest that honors students are more likely to participate in extracurricular activities.     I could also find the opposite result (students who are in clubs but not honors math) simply by switching the order of the source tables.     8 students are not in honors math. I can do future research to see if they’re in any other honors classes.     I’d also like to see which students are in honors math and at least one club. I can find this with the INTERSECT operator.     10 students participate in both.     Now, I’d like to see which students are in honors math and all three clubs. I can change my initial ALL_CLUBS logic to create a THREE_CLUBS table using the INTERSECT operator.     Only two students, Judy and Mary, are in all three clubs.     Then, I can combine ALL_MATH_H and THREE_CLUBS with an intersect to get my final answer.     Both Judy and Mary are in the results. This suggests that they’re exemplary, well-rounded students who might be eligible for certain awards or scholarships.     Alternatively, I could've found this result by doing a three-way intersect with ART, CROSS_COUNTRY, ZOOLOGY, and ALL_MATH_H, but I wanted to create and save the THREE_CLUBS table separately for additional processing later.   Summary   This is only one example of the possibilities with the Union Rows step. Though the logic itself is simple, this step can be used to handle some complex data prep and data consolidation tasks.   For more information on this step, visit the documentation.   Interested in SAS Studio Flows? Check out my previous post on the Merge Table step, plus other posts under the SAS Studio Flow tag.     Find more articles from SAS Global Enablement and Learning here. ... View more

  One feature of the DQSCHEME procedure is that schemes can be produced in one of two formats: SAS or QKB. While the QKB format is necessary for adding schemes to custom QKBs and working with schemes in SAS Data Quality software, QKB scheme files cannot be viewed or edited programmatically. If there are errors in the scheme, you might not find them until you've already applied your scheme and gotten unexpected results.   The CONVERT statement, which is used to switch the format type of a scheme, provides a helpful workaround. In this blog, I'll clean up our faulty QKB scheme from the previous blogs in this series. I’ll use the CONVERT statement to convert a QKB scheme to SAS format, which will allow me to edit the scheme programmatically. Once I’m satisfied with my scheme, I’ll convert it back to QKB format for future use.   If you haven’t already, check out my previous posts about the CREATE statement and the APPLY statement.   Using the CONVERT statement   The DQSCHEME procedure CONVERT statement is used to change the format of a scheme file. We’ve seen previously that schemes can be created in either SAS format (stored in SAS tables) or QKB format (stored in QKB scheme files). The CONVERT statement enables you to easily convert a scheme between SAS and QKB formats – you only need to provide the conversion type, the existing scheme name, and the converted output scheme name. There are no optional arguments for this statement.   CONVERT a QKB scheme file to SAS format   I mentioned the CONVERT statement as a scheme editing solution in my last blog after reviewing the undesired results from applying the cars QKB scheme. We can’t edit a QKB scheme file programmatically, but we can convert a QKB scheme to SAS format and then edit it programmatically like I did in part 1.   First, I’ll convert the cars.sch.qkb scheme file to SAS format. In the CONVERT statement, I’ll include the 3 required arguments: QKBTOSAS as my conversion type, the cars fileref as my existing scheme, and cars_QKBtoSAS as my converted scheme name.   filename cars "/home/student/cars.sch.qkb"; proc dqscheme; convert QKBTOSAS in=cars out=cars_QKBtoSAS; run;   In the log, I see a message about the meta options for the new scheme cars_QKBtoSAS. These are the same meta options stored in cars.sch.qkb.   Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   When viewing cars_QKBtoSAS, I see that the scheme has been converted successfully. This reveals the problems with the scheme, which are the same problems with our default SAS scheme from part 1: missing data for any versions of ‘Toyota Corolla” and inconsistency in final values.     I can view the table properties to confirm that the metadata options were stored successfully. Recall that metadata options are stored in data set labels.     Now that my scheme is in SAS format, I can make my desired changes. As a reminder, you can use any SAS method to update the format data set if you make sure to preserve the metadata options in the data set label. I’ll use PROC SQL.   proc sql; create table cars_QKBtoSAS_edit(label='"EX" "P" "" ""') as select data, propcase(standard) as STANDARD from cars_QKBtoSAS; insert into cars_QKBtoSAS_edit values('Toy. Corolla', 'Toyota Corolla') values('TOYOTA Corolla', 'Toyota Corolla'); quit;   My resulting scheme data set, cars_QKBtoSAS_edit, looks much better with the applied changes.     CONVERT a SAS scheme to QKB scheme file format   Now that we’re done, I can convert the edited scheme back to QKB format so that it’s ready for future use. This time, I’ll use SASTOQKB for the conversion type.   filename cars_fin "/home/student/cars_final.sch.qkb"; proc dqscheme; convert SASTOQKB in=cars_QKBtoSAS_edit out=cars_fin; run;   The log confirms that the scheme was successfully converted to QKB format and stored in cars_final.sch.qkb with the appropriate metadata options.     Considerations for converting scheme formats   In this post, I converted a scheme from QKB format to SAS format and then back to QKB format at the end. This is because I previously created a QKB scheme but I couldn't view or edit it programmatically, so I didn't find the problems in the scheme until I applied it in part 2. While this solution works, it's redundant when creating new custom schemes. A more efficient method is to create a SAS scheme first, then convert it to a QKB scheme once you have made any desired changes.   As mentioned in previous posts, you can use SAS Data Management Studio or the SAS QKB Definition Editor to create, edit, and apply QKB schemes. These tools provide a point-and-click alternative to the method shown in this blog while also eliminating the need for scheme format conversion.   Summary   The CONVERT statement enables you to change a scheme’s format quickly and easily. This simple statement can be helpful for editing QKB schemes or preparing SAS schemes for use in SAS Data Quality software. For more information on the CONVERT statement, review the documentation.   This post concludes my series on the DQSCHEME procedure statements. If you haven’t already, read part 1 on the CREATE statement and part 2 on the APPLY statement. Interested in learning more about SAS Data Quality programming tools and techniques? Check out the series Coding for Data Quality.       Find more articles from SAS Global Enablement and Learning here. ... View more

Data cleansing can feel like a chore when out-of-the-box tools don’t quite work for your data. There’s a simple solution for that problem: SAS Data Quality! SAS Data Quality enables you to customize or create brand new tools for data cleanup without ever leaving a programming window. In my last post, I introduced you to the DQSCHEME procedure and demonstrated how to create custom schemes with the CREATE statement.   Now, it’s time to put those schemes to use! I’ll show you how to use the APPLY statement to apply a scheme to an input variable with a variety of application methods.   For a refresher on the DQSCHEME procedure and its capabilities, review What is the DQSCHEME procedure? from part 1 or view the SAS documentation.   Using the APPLY statement   The DQSCHEME procedure APPLY statement is used to apply a scheme to an input variable. The only required arguments for the APPLY statement are the scheme name and the input variable name, but several optional arguments are available for controlling how the scheme is applied. By default, the values in the input variable will be transformed only if they match exactly with the pre-transformation values stored in the scheme. However, you could choose to ignore the case of the input values when transforming them, or you could choose to apply transformations after comparing input values and scheme values with match codes.   In this post, I’ll use the APPLY statement to apply the schemes I created in part 1 of this series. I’ll apply the scheme to a few different data sets, and I’ll use multiple scheme application methods.   APPLY a scheme in SAS format   In the previous post, I created a SAS scheme based on the CARS data set, which contains car model names. After modifying the generated scheme to clean up the default results, my final scheme was named CARS_SCHEME_EDIT. I’ll apply this to the CARS data set and save the results in an output data set named CARS_STD_SAS. Recall our original data:   Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   Since I’m using a SAS scheme, I’ll need to include the NOQKB option on the PROC DQSCHEME statement.   proc dqscheme data=cars out=cars_std_sas noqkb; apply scheme=cars_scheme_edit var=model; run;   Performing this basic application of this scheme yields my desired results.     I have another data set named CARS_2 that contains the same car model names, but the values have varied casing that isn’t reflected in my original scheme.     No worries! I’ll add the SCHEME_LOOKUP=IGNORE_CASE option to the APPLY statement, which will ignore any capitalization when applying the scheme.   proc dqscheme data=cars_2 out=cars_2_std_sas noqkb; apply scheme=cars_scheme_edit var=model scheme_lookup=IGNORE_CASE; run;   The results show that my scheme has been applied flawlessly despite casing differences.     I have a third data set CARS_3 that, again, contains the same car model names, but there are typos in nearly every value (most of which aren’t accounted for in my scheme).     This is where the match code scheme application method comes in handy. Since an equal comparison won’t work for applying the scheme here, we can instead compare input value match codes against scheme value match codes. Input values will be transformed based on equal match code values. I’ll add the SCHEME_LOOKUP=USE_MATCHDEF option to the APPLY statement. Then, I’ll add the match definition and locale for generating match codes (I’ll skip the optional sensitivity argument since I want to use the default value, 85).    proc dqscheme data=cars_3 out=cars_3_std_sas noqkb; apply scheme=cars_scheme_edit var=model scheme_lookup=USE_MATCHDEF matchdef='Text' locale='ENUSA'; run;   Once more, my results show that my scheme was applied to this data with no problems despite the variation in input values.     APPLY a scheme in QKB format   In part 1, I also created a scheme in QKB format and stored it in the scheme file cars.sch.qkb. I noted then that QKB schemes cannot be edited programmatically. Since this series focuses on the programmatic use of schemes, I did not edit my QKB scheme with other methods. This means my QKB scheme will have the same problems my default SAS scheme had. I’ll come back to that issue shortly.   To apply a QKB scheme, I’ll add the QKB option on the PROC DQSCHEME statement (though this is the default scheme type option). I’ll also add my QKB scheme name and my input variable name to the APPLY statement. Remember that the easiest way to work with a QKB scheme file is to give it a fileref first with the FILENAME statement.   filename cars "/home/student/cars.sch.qkb"; proc dqscheme data=cars out=cars_std_qkb qkb; apply scheme=cars var=model; run;   The output data shows that the scheme was applied successfully. Because I haven’t edited my QKB scheme, the results differ from what I want. I want to edit my scheme to have a consistent casing standard and include rules for the different ‘Toyota Corolla’ values, like I did with my SAS scheme in part 1.     So, what can we do? Are we stuck with a scheme that’s not quite right until we plug it into the SAS QKB Definition Editor for editing? The answer is no! You can use the CONVERT statement to aid in creating and editing your QKB schemes programmatically, which I’ll explore in my next blog.   To get a head start on learning about the CONVERT statement, check out the documentation.   Considerations for applying schemes   One DQSCHEME procedure step can contain any combination of CREATE, APPLY, and CONVERT statements. While schemes can be created and applied in the same PROC DQSCHEME step, this is not recommended. By creating and applying a scheme in one go, you forfeit the chance to view and edit your scheme before applying it. If you don’t like the scheme, you will have transformed your data to an undesired result—which means more work for you. Always create and apply schemes in separate steps.   When applying a scheme, be wary that the match code application method will be more resource intensive than the default method. Depending on the size of your data, your processing time could be significantly increased. Instead, updating your original scheme and then applying the updated scheme with the default method may be more efficient.   Summary   The APPLY statement can be used to apply SAS or QKB schemes to an input variable. The SCHEME_LOOKUP argument offers you flexibility in applying your custom schemes. This optional argument helps to ensure that your input data will be transformed even if it doesn’t perfectly align with your scheme data. For more information on the APPLY statement, including optional arguments that weren’t covered in this post, view the documentation. Look out for part 3, which will cover the PROC DQSCHEME CONVERT statement.     Find more articles from SAS Global Enablement and Learning here. ... View more

Have you ever encountered a data set that is inconsistent in how it represents the same piece of information? For example, maybe a person’s name has been written in all caps one time when every other occurrence is in proper case, or a state is listed with both its full name and its abbreviation. When a data set contains repeated values that are spelled, cased, or formatted differently, it can cause problems down the road.   Don’t fret! SAS Data Quality provides multiple options for standardizing your data which make data cleansing easier than ever. Today, I’ll discuss the multi-faceted DQSCHEME procedure and the capabilities of the CREATE statement.   What is the DQSCHEME procedure?   The DQSCHEME procedure is used to create, apply, and convert standardization schemes. Standardization schemes specify how different versions of the same value will be transformed in your data. These schemes are simple lookup tables that contain a list of values and what each value will be transformed or standardized to. When applied to a variable, all relevant values will be replaced with the standard value from the scheme.   To create a scheme, PROC DQSCHEME takes your input variable and generates match codes for each value using a provided match definition. Data values in the variable are clustered based on the match codes. For each cluster group, the most common iteration of the value in the group is chosen as the standard value and stored in the scheme. Values without a cluster (i.e., unique values) are not stored in the scheme by default. After creation, the scheme can be applied to a data set variable (often the variable that was used to create the scheme).   Note that PROC DQSCHEME will not perform any standardization operations on the standard values in a scheme. This means that the set of standard values may not have consistent casing, formatting, etc. depending on the data used to create the scheme. We’ll see an example of this shortly.   For more information on the DQSCHEME procedure, view the documentation.   Using the CREATE statement    The DQSCHEME procedure CREATE statement has two main purposes: creating analysis data sets and creating schemes. In this blog, I'll use the CREATE statement to first make an analysis data set based on my source data. This will show how my data is grouped together and what the most common values are for each group, along with any data quality issues that might impact my scheme. Then, I'll use the CREATE statement to make schemes in two formats: SAS format and QKB format. Finally, I'll modify the schemes programmatically where possible to correct issues that were uncovered in the analysis.   CREATE an analysis data set   Before creating a scheme, you can analyze your input data. This gives you a peek behind the curtain of scheme creation by showing you which data values will be clustered and what value they will standardize to.   The output data set will show the number of occurrences of each unique value in the analysis variable and how they are grouped together. The most common iteration of a value will be chosen as the standard value in the scheme. If you aren’t satisfied with the default result, you can adjust the match code sensitivity with the optional SENSITIVITY= argument. This can be useful if you feel that the default sensitivity level leads to overmatching (clustering values that are not similar) or undermatching (not clustering values that are similar) in your data set.   For example, say I’m working with a data set that has a list of some popular car models. There's quite a bit of variation in casing and spelling through the list, including shortened names and misspelled names. I’d like to create a scheme to standardize this data, and I’ll start by creating an analysis data set for the model variable.       Using the cars dataset in PROC DQSCHEME, I’ll create an analysis data set named cars_result. I’ll use the model variable and the 'Text' match definition.   proc dqscheme data=cars; create analysis=cars_result matchdef='Text' var=model locale='ENUSA'; run;   My result has three variables: count, model (my input variable), and cluster. The unique values are grouped based on match codes and listed in order of descending count.       This result tells me that my standard values will be Honda Civic and JEEP GRAND CHEROKEE, with no standard value for any version of Toyota Corolla because there was no cluster formed for it. This is where creating an analysis data set is helpful. I know that if I create a scheme with this input variable, my scheme will have an inconsistent standard and some missing information. I’ll touch on how to handle this situation later. Next, let’s see how to create schemes.     CREATE a scheme in SAS format   A scheme in SAS format is simply a scheme stored in a SAS data set. You’ll need to specify your input variable, a match definition, and a scheme name, along with any desired optional arguments. You’ll also need to include the NOQKB option on the PROC DQSCHEME statement.   Let’s create a scheme in SAS format for the model variable.   proc dqscheme data=cars noqkb; create matchdef='Text' var=model scheme=cars_scheme locale='ENUSA'; run;   My result has two variables: data and standard. Data values are listed in alphabetical order.     As I predicted from the analysis earlier, the standard is inconsistent. The Honda Civic standard value is written in proper case, the JEEP GRAND CHEROKEE standard value is written in uppercase, and there is no standard value for any version of Toyota Corolla. I can fix this by editing the scheme data set.   There are many options for editing and appending a SAS data set. In this example I'll use PROC SQL to change the standard column values to proper case and add new rows for my Toyota Corolla standard.   Note that metadata options are stored in scheme data set labels, so you will need to add a label to the output data set to preserve those options if you are editing the table by making a copy of it. In this example, I’ll copy the data set label from cars_scheme.   proc sql; create table cars_scheme_edit(label='"EX" "P" "" ""') as select data, propcase(standard) as STANDARD from cars_scheme; insert into cars_scheme_edit values('Toy. Corolla', 'Toyota Corolla') values('TOYOTA Corolla', 'Toyota Corolla'); quit;   CREATE a scheme in QKB format   QKB format schemes are stored in QKB scheme files, which can then be added to a QKB. This enables you to use your schemes in SAS Data Management Studio.   The simplest way to create a QKB scheme programmatically is to first create a file reference (fileref) that points to a scheme file. Note that filerefs can only be up to 8 characters long and the scheme file must end with the suffixes .sch.qkb. It’s recommended to store the scheme file in a QKB scheme folder.   filename cars "/home/student/cars.sch.qkb";   Once the fileref has been created, I can create the scheme. I'll invoke the QKB option on the PROC DQSCHEME statement though this is the default option.   proc dqscheme data=cars qkb; create matchdef='Text' var=model scheme=cars locale='ENUSA'; run;   You won’t get data set output from creating a QKB scheme, so you’ll have to check the log output to ensure that the scheme was created successfully.     You cannot edit a QKB scheme programmatically. If you wish to view or edit the scheme, you’ll have to use SAS Data Management Studio or the SAS QKB Definition Editor. To add the scheme to your QKB, the QKB admin must copy the file into the source QKB folder structure, then redeploy the QKB. This process does not need to be completed to use the scheme programmatically, but it will need to be done to use the scheme in any SAS Data Management Studio software. For more information on editing a scheme with SAS Data Management Studio, view the documentation.   Summary   In conclusion, the PROC DQSCHEME CREATE statement can be used to create an analysis data set, to create a SAS scheme, and to create a QKB scheme. Optional arguments can control how your scheme is created, and you can edit your scheme afterwards if you aren’t satisfied with it. For more information on the CREATE statement, view the documentation. Stay tuned for future blogs discussing the PROC DQSCHEME APPLY and CONVERT statements.   ... View more

SAS Studio Flows provides a series of point-and-click steps that simplify the process of working with your data. In this post, I'll show you an example of performing an "upsert" (update and insert) on my data using the Merge Table step.   With the Merge Table step, you can update a target table with data in a source table. The Merge Table step allows you to update values in existing rows, insert new rows, or do both within one step. There are multiple benefits to this, including reducing the number of steps in your flow and optimizing processing via in-database execution.   To use the Merge Table step, you'll need a SAS Studio Engineer license. Additionally, this step can only be used with Oracle, Teradata, Snowflake, and SQL Server tables at the time of publication. To see the most current list of acceptable data types, refer to the documentation.   For my example, I'll be using the target table MARKETPRODUCTS which contains information for products being sold at a market and the source table MARKETPRODUCTUPDATE which contains updated product information.   I start by adding MARKETPRODUCTUPDATE to the Flow canvas.     Then, I add the Merge Table step and connect the source table to the input port.     After the source table is connected, I select my target table MARKETPRODUCTS on the Target Table tab. Note that you must select a table that is the same type and from the same database as your source table.     Once my tables are selected, I can configure my merge conditions on the Options tab. The first step is to choose a key column from the target table, which is the column used to match rows between the source and target tables. The key column must have a matching column in the source table, and it must contain unique identifying information for each row. I choose the ID column, which contains each product's unique numeric ID.     Next, I add my update and insert conditions. You must add at least one update or insert condition for the Merge Table step to run, but you do not have to choose both. In the Update section on the Options tab, I'll choose to update the values in the Product and Price columns.   Note here that the source table does not have a Price column, but it has a column named Cost which contains the same information, so I manually select them to map, or match together.     In the Insert section, I choose all columns to insert new row values into. You have the freedom to select only a few columns if you don't want full new rows to be inserted into your target table. For example, I could choose to include only ID and Product values in new rows if I plan to add a new price later.     I can look at column attributes of my target table on the Column structure tab.     I can also check the column resolution between my source and target tables on the Column resolution tab. Note that you cannot adjust column mapping on this tab, but you can view it. Here, I can see that the Price column does not have a match in the source table and the Cost column is ignored during mapping because it does not exist in the target table.     Lastly, I can see a snippet of my target table data on the Preview Data tab. This is the target table before the flow has run.     After running the flow, I can view the changes made to my target table. I can see updated values and new rows.     SAS Studio automatically generates code for this flow, which you can view for reference or save for future use.     What about the Load Table step? For those who are familiar with the data integration steps in Flows, you might find that this step's functionality sounds similar to the Load Table step. So, what's the difference?   The Load Table step does not have a limit on what type of table can be used with it, while the Merge Table step does. However, the Load Table step does not have the column flexibility that the Merge Table step does. Any source columns that do not match exactly with target columns will be ignored during loading and you cannot manually match columns in the Options tab. Additionally, you cannot manually select columns to update or insert into. Lastly, Load table has multiple output table options (and preprocess options if you're loading with the insert rows technique) while Merge Table does not.   For more information on Load Table, view this post.   In summary... The Merge Table step can be used to update, insert, or upsert values in your Oracle, Teradata, Snowflake, or SQL Server tables. The flexibility and simplicity of this step makes it a great addition to your point-and-click data integration toolbelt!   For more information on this step, refer to the documentation. An accompanying video demonstration can be found here.   Find more articles from SAS Global Enablement and Learning here. ... View more