Activities and Practices (with Solutions) for SAS® SQL 1: Essentials

To complete the activities, demos, and practices in this course, you must access SAS software and set up your practice files.

Using SAS OnDemand for Academics

Using your own SAS software

Start your SAS software. Click Open next to the SAS software interface you have, and follow the instructions to set up your data.

Open s101a04.sas from the activities folder and perform the following tasks to explore the sq.customer table:

Remove the asterisk and select only the FirstName, LastName, and State columns. Run the query and view the log and results.



proc sql;
select FirstName, LastName, State
    from sq.customer(obs=10);
quit;

Remove the OBS=10 data set option and add the INOBS=10 PROC SQL option after the PROC SQL keywords and before the semicolon. Run the query. Are the results the same using the INOBS=10 option? What about the log?



proc sql inobs=10;
select FirstName, LastName, State
    from sq.customer;
quit;


The INOBS= option restricts the number of rows that PROC SQL retrieves from any single source. The results are the same, but the log returns a warning.

After the INOBS= option, add the NUMBER option. Run the query. Which column was added to the results?



proc sql inobs=10 number;
select FirstName, LastName, State
    from sq.customer;
quit;


The Row column was added. The NUMBER option specifies whether the SELECT statement includes the row number of the data as the rows are retrieved.

Open s102a01.sas from the activities folder and perform the following tasks to find all customers in the states VT, SC, or GA:

Complete the WHERE clause to filter for customers in the state of VT and run the query. How many customers are from VT?



where State = "VT"


47 customers

Add another expression using the OR operator to select only customers from the state of VT or SC. How many customers are from either VT or SC?



where State = "VT" or State = "SC"


792 customers

Switch your current expression to use the IN operator. Add the state of GA. How many customers are from either VT, SC, or GA?



where State in ("VT", "SC", "GA")


2,704 customers

Open s102a02.sas from the activities folder and perform the following tasks to find all customers with a nonmissing CreditScore value that is less than 500:

Examine the query. Add a WHERE clause to find all customers with a CreditScore value that is less than 500 and run the query. What do you notice about the values in the CreditScore column? How many rows are in your report?



where CreditScore < 500


Missing values are included in the results, for a total of 2,197 rows.

Include the AND operator in the WHERE clause to find all rows that are less than 500 and not null. Use a method of your choice. How many rows are in your final report?



where CreditScore < 500 and CreditScore is not null
where CreditScore < 500 and CreditScore is not missing
where CreditScore < 500 and CreditScore ne .


These are equivalent statements. Missing values are not included in the results. The new report contains 196 rows.

Open s102a03.sas from the activities folder and perform the following tasks to sort the report by CreditScore and LastName:

Complete the ORDER BY clause and sort by CreditScore. Run the query and examine the report. What is the default sort order?



select FirstName, LastName, CreditScore
    from sq.customer
    where CreditScore > 830
    order by CreditScore;


Ascending

Add the keyword DESC after the CreditScore column in the ORDER BY clause. Run the query and examine the report. What does the DESC option do?



select FirstName, LastName, CreditScore
    from sq.customer
    where CreditScore > 830
    order by CreditScore desc;


It changes the sort order to descending.

Add a secondary sort column to sort by LastName. Run the query. Who is the first customer on the report?



select FirstName, LastName, CreditScore
    from sq.customer
    where CreditScore > 830
    order by CreditScore desc, LastName;


Donald Leyva

Remove LastName from the SELECT clause and rerun the query. Are the results still sorted by LastName within CreditScore?



select FirstName, CreditScore
    from sq.customer
    where CreditScore > 830
    order by CreditScore desc, LastName;


Yes, you can sort by any columns in the input table even if they are not in the SELECT clause.

Open s102a04.sas from the activities folder and perform the following tasks to enhance a report:

Examine the query. Add the title "Customers from Hawaii" and a footnote using today's date. Run the program and examine the new title and footnote in your report.



title "Customers from Hawaii";
footnote "March 6";
proc sql;
select FirstName, LastName, State, 
       UserID /*Add label*/, 
       Income /*Add label and format*/, 
       DOB /*Add format*/
    from sq.customer
    where State = "HI" and 
          BankID is not null
    order by Income desc;
quit;

title; 
footnote; 

Apply LABEL="Email Address" to the UserID column and LABEL= "Estimated Income" to the Income column.



title "Customers from Hawaii";
footnote "March 6";
proc sql;
select FirstName, LastName, State, 
       UserID label="Email Address", 
       Income label="Estimated Income", 
       DOB /*Add format*/
    from sq.customer
    where State = "HI" and 
          BankID is not null
    order by Income desc;
quit;

title; 
footnote;

Apply FORMAT=DATE9. to the DOB column and FORMAT=DOLLAR16.2 to the Income column. Run the program and examine the report.



title "Customers from Hawaii";
footnote "March 6";
proc sql;
select FirstName, LastName, State, 
       UserID label="Email Address", 
       Income label="Estimated Income" format=dollar16.2, 
       DOB format=date9.
    from sq.customer
    where State = "HI" and 
          BankID is not null
    order by Income desc;
quit;

title; 
footnote;

Change the DOLLAR16.2 format to DOLLAR7.2. Run the program. What happens to the values in the Income column?



title "Customers from Hawaii";
footnote "March 6";
proc sql;
select FirstName, LastName, State, 
       UserID label="Email Address", 
       Income label="Estimated Income" format=dollar7.2, 
       DOB format=date9.
    from sq.customer
    where State = "HI" and 
          BankID is not null
    order by Income desc;
quit;

title; 
footnote;


The values are missing commas and dollar signs, and they contain only one decimal point.

Open s102a05.sas from the activities folder and perform the following tasks to find all customers 70 years old and older:

Examine and run the query. View the results.

Add the expression yrdif(dob,'01jan2019'd) in the SELECT clause after UserID to create a new column. Run the query and examine the results. What is the name of the new column?



proc sql;
    select FirstName, LastName, UserID,
           yrdif(dob,'01jan2019'd)
    from sq.customer(obs=100);
quit;


Without the AS keyword, the new column does not have a name.

Add as Age after your function. Run the query and examine the results. What changes?



proc sql;
    select FirstName, LastName, UserID,
           yrdif(dob,'01jan2019'd) as Age
    from sq.customer(obs=100);
quit;


The AS keyword specifies a column name for the new column.

Remove the OBS= data set option in the FROM clause and add a WHERE clause to return rows where Age is greater than or equal to 70. Run the query. Did the query run successfully?



proc sql;
    select FirstName, LastName, UserID,
           yrdif(dob,'01jan2019'd) as Age
    from sq.customer(obs=100)
    where Age > 70;
quit;


No. The WHERE clause is evaluated before the SELECT clause. Therefore, columns used in the WHERE clause must exist in the table listed in the FROM clause.

Open s102a06.sas from the activities folder and perform the following tasks to eliminate duplicate values in a table:

Examine and run the query. View the results.

Change the State column in the SELECT clause to the Employed column. Run the query. What does this query show?



select distinct Employed


It displays the unique values of N or Y in the Employed column.

Add the Married column in the SELECT clause after the Employed column. Run the query. What does this query show?



select distinct Employed, Married


It displays unique combinations of Employed and Married values.

Open s102a07.sas from the activities folder and perform the following tasks to summarize a table using the COUNT function:

Examine and run the query. View the results. Why is the value of MaritalStatus different from the value of TotalRows?


TotalRows counts all rows in the table. MaritalStatus counts every nonmissing occurrence in the Married column.

Inside the COUNT function, add the DISTINCT keyword in front of the Married column and run the query. What does the new report show?



select count(*) as TotalRows format=comma10.,
       count(distinct Married) as MaritalStatus format=comma10.
    from sq.customer;


You can use DISTINCT with the COUNT function to return the number of distinct, nonmissing values from a column.

Open s102a08.sas from the activities folder and perform the following tasks to summarize data using date functions:

Examine and run the query. View the results. Which month has the highest value for MedianSpent?


Month 2, February

Replace the MONTH function with the QTR function. Change the name of the Month column to Qtr. Run the query. What is the error?



proc sql;
select qtr(datepart(DateTime)) as Qtr, 
       Median(Amount) as MedianSpent format=dollar16.
    from sq.transaction
    group by Month;
quit;


The following columns were not found in the contributing tables: Month.

Replace Month in the GROUP BY clause with Qtr. Run the query. Which quarter has the highest value for MedianSpent?



proc sql;
select qtr(datepart(DateTime)) as Qtr, 
       Median(Amount) as MedianSpent format=dollar16.
    from sq.transaction
    group by Qtr;
quit;


Qtr 1

Open s102a09.sas from the activities folder and perform the following tasks:

Examine and run the query in the Create a Table from a Query section. View the results.

Add the CREATE TABLE statement and create a table named Top5States. Run the query and confirm that the table was created successfully.



proc sql;
create table Top5States as
...

Run the code below your SQL query. What did the code produce?


The SGPLOT procedure created a visualization of the newly created table.

Open s102a10.sas from the activities folder and perform the following tasks to create a new table and insert rows into it:

Examine the CREATE TABLE statement and run the query only. Confirm that an empty table was created.


There are four columns and zero rows in the new highcredit table.

In the Inserting Rows with a Query section, enter the correct column names to complete the INSERT INTO statement. Run the query. How many rows were inserted into the table highcredit?



insert into work.highcredit(FirstName,LastName,
                            UserID,CreditScore)
    select FirstName, LastName,  
           UserID, CreditScore
    from sq.customer
    where CreditScore > 700;


26,006 rows were inserted into work.highcredit.

In the Inserting Rows with the SET Clause section, complete the INSERT INTO statement with the SET clause and insert yourself as a customer into the highcredit table. Run the query. What does the note in the log say?


NOTE: 1 row was inserted into WORK.HIGHCREDIT.

Complete the code to drop the highcredit table.



proc sql;
    drop table highcredit;
quit;

Open s102a11.sas from the activities folder and perform the following tasks to find all the available DICTIONARY tables in your SAS session:

Examine and run the program. View the log and results.

In the log, note the column labels for the first two columns: Member Name is the DICTIONARY table, and Data Set Label is the description of that table.

Replace the asterisk in the SELECT clause and select the distinct memname and memlabel columns. Run the query and examine all the available DICTIONARY tables in your SAS session.



select distinct memname, memlabel

What is the data set label of the MEMBERS DICTIONARY table?


Tables, catalogs, and views

Open s103a01.sas from the activities folder and perform the following tasks to perform a default join of two tables:

Examine and run the two queries to explore the sq.smallcustomer and sq.smalltransaction tables. Confirm that the sq.smallcustomer contains 8 rows and the sq.smalltransaction contains 12 rows.

In the next section, list the sq.smallcustomer and sq.smalltransaction table in the FROM clause and separate the tables by a comma. Run the query and view the log. What note do you see?



proc sql number;
    select *
    from sq.smallcustomer, sq.smalltransaction;
quit;


Note: The execution of this query involves performing one or more Cartesian product joins that can not be optimized.

View the results. Name two issues with the report.


Redundant columns, nonmatching IDs

Open s103a02.sas from the activities folder and perform the following tasks to perform an inner join on tables with different columns names:

Examine and run the two queries to explore the sq.statepopulation and sq.statecode tables. What columns can you use to join the tables?


You can use the Name column from the statepopulation table and StateCode column from the statecode table.

In the Complete the Inner Join section, specify the tables in the FROM clause and perform an inner join. Add the alias p for the sq.statepopulation table, and the alias s for the sq.statecode table.



from sq.statepopulation as p inner join sq.statecode as s

Complete the ON expression to match rows where p.Name = s.StateCode. Highlight and run the query. How many rows are in the new report?



on p.Name = s.StateCode


52

Open s103a03.sas from the activities folder and perform the following tasks to find tables that contain BankID and MerchantID columns:

Complete the first query by adding the BANKID column name in the WHERE clause. How many tables contain the BankID column?



where libname="SQ" and upcase(name)="BANKID";


eight

Replace BANKID with MERCHANTID. How many tables contain the MerchantID column?



where libname="SQ" and upcase(name)="MERCHANTID";


five

Open s103a04.sas from the activities folder and perform the following tasks to use a non-equijoin:

Complete the ON clause to join on rows where customer Income is greater than the LowIncome range, and less than or equal to the HighIncome range using the BETWEEN-AND WHERE operator.



on c.Income between t.LowIncome and t.HighIncome

What tax bracket is Olga Comstock in?


12%

View your log. What note do you see?


NOTE: The execution of this query involves performing one or more Cartesian product joins that can not be optimized.

Open s103a05.sas from the activities folder and perform the following tasks to perform a left join:

Run the query to create a left join between the sq.smallcustomer and sq.smalltransaction tables. Notice the difference within the AccountID columns in rows 8 and 9.


The left join selects all customers with or without a transaction.

Remove the column t.AccountID in the SELECT clause. Run the query and examine the results. How many missing AccountID values are in the results?



proc sql number;
    select FirstName, LastName, Income,  
           c.AccountID "c.AccountID",
           DateTime, MerchantID, Amount 
    from sq.smallcustomer as c left join 
         sq.smalltransaction as t
    on c.AccountID = t.AccountID;
quit;


There are no missing AccountID values. The query selects AccountID values from the smallcustomer table.

Replace c.AccountID with t.AccountID. Replace the c in the column label with a t. How many missing AccountID values are in the results?



proc sql number;
    select FirstName, LastName, Income,  
           t.AccountID "t.AccountID",
           DateTime, MerchantID, Amount 
    from sq.smallcustomer as c left join 
         sq.smalltransaction as t
    on c.AccountID = t.AccountID;
quit;


There are two missing AccountID values. The query selects AccountID values from the smalltransaction table.

Open s103a06.sas from the activities folder and perform the following tasks to find all transactions not associated with a documented customer:

Run the query to create a left join between the sq.smalltransaction2 and sq.smallcustomer2 tables. Examine the report. Notice that the rows with missing values in AccountID have been joined.

In the ON clause, add the expression and t.AccountID is not null. Run the query. Confirm that missing values were not joined.



on c.AccountID = t.AccountID and
   t.AccountID is not null;

Add a WHERE clause with the expression c.AccountID is null to filter for all transactions without a documented customer. Run the query and examine the report. How many transactions do not have a customer associated with them?



where c.AccountID is null;


seven

Open s103a07.sas from the activities folder and perform the following tasks to join two tables using the SUBSTR functionr:

Examine and run the query. Did you receive a syntax error?


No syntax error, but no rows were returned from the query.

In the ON clause, use the SUBSTR function on t.StateID to extract the first two characters. Run the query. Which StateName is Caberto, Glen Daniel from?



on substr(t.StateID,1,2) = s.StateCode;


California

Open s103a08.sas from the activities folder and perform the following tasks to join a numeric column with a character column:

Run the queries in the Create a Table and Insert Values section. View the newly created table.

Run the query in the Join Different Column Types section. What syntax error was generated when you joined columns of different types?


ERROR: Expression using equals (=) has components that are of different data types.

Leave the program open for the next Activity.

Open s103a08.sas from the activities folder and perform the following tasks to join a numeric column with a character column by using the PUT function:

If you have not run the queries in the Create a Table and Insert Values section, run those now.

Use the PUT function to convert z.Zip in the ON clause to a character value using the z5 format. Run the query.



on c.ZipCode = put(z.Zip,z5.);

What city does the ZipCode value 14216 represent?


Buffalo

Open s104a01.sas from the activities folder and perform the following tasks to use a subquery to return two columns in the WHERE clause:

Examine and run the first query. Confirm that the results contain one row and two columns.

Add the first query as a subquery in the second query to find all states with PopEstimate1 values higher than the average estimated state population.



where PopEstimate1 > (select avg(PopEstimate1), "Average Estimated Population"
    from sq.statepopulation);

Run the query. What is the syntax error in the log?


ERROR: A subquery cannot select more than one column.
ERROR: A Composite expression (usually a subquery) is used incorrectly in an expression.

This error occurs because we selected two columns in this subquery. The subquery can only return one column.

Open s104a02.sas from the activities folder and perform the following tasks to use a subquery in the HAVING clause:

Examine and run the first query. View the results.

Modify the second query. Copy the value returned by the first query into the subquery against the HAVING clause to return divisions with an average PopEstimate1 value greater than the total average of PopEstimate1. Run the query. How many divisions have a higher average PopEstimate1 value than the average PopEstimate1 value of all the states?



proc sql;
select Division, avg(PopEstimate1) as avgDivisionPop
    from sq.statepopulation
    group by Division
    having avgDivisionPop > 6278420;
quit;


Five divisions have a higher average PopEstimate1 value than the average PopEstimate1 value of all the states.

Remove the static value and add the subquery in the HAVING clause. Run the query.



proc sql;
select Division, avg(PopEstimate1) as avgDivisionPop
    from sq.statepopulation
    group by Division
    having avgDivisionPop > (select avg(PopEstimate1)
        from sq.statepopulation);
quit;

How many divisions have a higher average PopEstimate1 value than the average PopEstimate1 value of all the states?


five divisions

Open s104a03.sas from the activities folder and perform the following tasks to find all states with a PopEstimate1 value that is lower than the value for New York or Florida:

Complete the query using the ANY keyword or MAX statistic. Run the query.



where PopEstimate1 < any (select PopEstimate1
                              from sq.statepopulation
                              where Name in ("NY","FL"));

or



where PopEstimate1 < (select max(PopEstimate1)
                          from sq.statepopulation
                          where Name in ("NY","FL"));

How many states have estimated populations lower than New York or Florida?


49 states

Open s104a04.sas from the activities folder and perform the following tasks to create and use a view:

Create a view named VWtotalcustomer from the query. Run the query and examine the log.



create view VWtotalcustomer as


Note: SQL view WORK.VWTOTALCUSTOMER has been defined.

Run the code in the section Use the View in the PROCS Below. Which state has the highest number of customers?


California

Open s104a05.sas from the activities folder and perform the following tasks to disable remerging of summary statistics:

Examine and run the query. What note do you see in the log?



NOTE: The query requires remerging summary statistics back with the original data.

Add the PROC SQL option NOREMERGE. Run the query. Did it run successfully? What was the error in the log?



proc sql noremerge;


No. The NOREMERGE option disables remerging of summary statistics.

ERROR: The query requires remerging summary statistics back with the original data. This is disallowed due to the NOREMERGE proc option or NOSQLREMERGE system option.

Add a GROUP BY clause after the FROM clause and group by Region. Run the query. Did it run successfully?



group by region;


Yes. The data is not remerged when you use the GROUP BY clause.

Open s105a01.sas from the activities folder and perform the following tasks to find unique customers who have responded by phone and email:

Run the first queries to preview the sq.salesemail and sq.salesphone tables. Examine the columns in both tables.

In the Intersect section, examine and run the query. Did the query run successfully? Why not?



No. ERROR: Column 2 from the first contributor of INTERSECT is not the same type as its counterpart from the second.

By default, the INTERSECT set operator matches by column position, and the data must be of the same type. EmailResp is character, and SalesRep is numeric.

Add the CORR keyword after the INTERSECT set operator. Run the query. Did the query run successfully? Why?



intersect corr
 

Yes. The CORR keyword matches columns by name, and nonmatching columns are removed from the intermediate result sets.

Open s105a02.sas from the activities folder and perform the following tasks to find all target customers who have not responded to our sales phone call:

Run the first queries in the Preview Tables section to preview the sq.salesemail and sq.salesphone tables. Examine the columns in both tables.

In the EXCEPT section, complete the query to find all customers from the sq.saleslist table who have not responded to our sales call in sq.salesphone.



select *
    from sq.saleslist
except corr
select *
    from sq.salesphone;    


OR



select CustomerID
    from sq.saleslist
except
select CustomerID
    from sq.salesphone;

How many customers have not responded to our phone call?


five

Open s106a01.sas from the activities folder and perform the following tasks to create and use user-defined macro variables:

Run the program. Examine the log and output data. Confirm that the name of the newly created table is customerga and contains 957 rows.

Replace the values GA and 650 in the %LET statements with NC and 700. Run the program. Examine the log and output data. What is the name of the newly created table? How many rows?



%let State=NC;
%let CreditMin=700;


The table name is customernc and it has 700 rows.

Change the double quotation marks in the WHERE clause expression to single quotation marks. Run the query. How many rows are in the new table?



where State='&State' and 
      CreditScroe > &CreditMin;


Zero rows are returned. The macro variable doesn't resolve single quotation marks. The WHERE clause is searching for the character string '&State'.

Open s106a02.sas from the activities folder and perform the following tasks to store a number larger than eight digits in a macro variable:

Run the program in the STEP 1 section to create the macro variables MaxPop and TotalCtry. The macro variables store the estimated maximum three-year population estimate for a country and the total number of countries. View the log. Notice that MaxPop stores a value in scientific notation.


When storing a number, SAS uses the BEST8. format. If the number is larger than eight digits, scientific notation is often used.

Examine STEP 2 of the program to find the country with the maximum estimated three-year population. Run the program to use the macro variables that you created. Confirm that no rows were returned.


No rows were selected. The scientific notation value is different from the original, and thus, SAS doesn't find a match.

Add the FORMAT=10. column modifier to the max(estYear3Pop) column in STEP 1. Run the entire program. Which country has the largest three-year estimated population?



select max(EstYear3Pop) format=10.,
       count(distinct CountryCode)          


China

Open s107a01.sas from the activities folder and perform the following tasks to pass Microsoft Access SQL to the database:

Note: You will be NOT be able to do this activity if you are using SAS OnDemand for Academics because this platforms does not have the software necessary to read Microsoft Access files. In addition, you might not be able to do this activity if you are using your own installation of SAS Enterprise Guide.

Examine the native DBMS query. Run the entire query. Did it produce an error?


Yes

ERROR: CLI describe error: [Microsoft][ODBC Microsoft Access Driver] Invalid use of IS operator in query expression 'BankID is missing'.

In the WHERE clause, replace IS MISSING with IS NULL. Run the entire query. Did it run successfully?



where BankID is null


Yes. IS MISSING is a SAS enhancement. IS NULL is ANSI standard.

Open a browser and perform the following tasks to view SAS DBMS-Specific Reference documentation:

Note: You will be NOT be able to do this activity if you are using SAS OnDemand for Academics because this platforms does not have the software necessary to read Microsoft Access files. In addition, you might not be able to do this activity if you are using you own installation of SAS Enterprise Guide.

Access SAS Help at http://support.sas.com/documentation.

Under Popular Documentation, select Programming: SAS 9.4 and Viya.

Scroll down until you find the Accessing Data section. Inside the section, find SAS/ACCESS and select Relational Databases.

Scroll down and find the DBMS-Specific Reference section and select a reference of your choice. Review the topic.

Click the Passing SAS Functions to link and review the SAS functions that pass to your DBMS for processing.

Open s107a03.sas from the activities folder and perform the following tasks to perform a PROC FEDSQL query:

Examine and run the query. Did it produce an error?


Yes

In the WHERE clause, replace the double quotation marks around NC with single quotation marks. Run the entire query. Did it run successfully?



where State='NC'


Yes. Double quotation marks around a character string is a SAS enhancement, and it produces an error when using PROC FEDSQL. Single quotation marks around a character string is ANSI standard and required in FEDSQL.