We’re smarter together. Learn from this collection of community knowledge and add your expertise.

SAS SQL: An Introduction – Ask the Expert Q&A

by SAS Employee CJ_SAS a week ago - edited a week ago by Community Manager (496 Views)

Did you miss the Ask the Expert session on Using SQL within SAS? Not to worry, you can catch it on-demand at your leisure.

 

 Watch the webinar

 

 The session covers basic SQL concepts, including:

  • Basic SQL queries and syntax.
  • Creating columns.
  • Subsetting and conditional logic.
  • Introduction to joins.
  • Creating macro variables.

 

Here’s a transcript of the Q&A segment held at the end of the session for ease of reference.

  1. ­When will SQL accept windowing functions?­

SAS SQL does not currently support windowing functions. Please see the following post for more information:

https://communities.sas.com/t5/SAS-Procedures/PROC-SQL-and-Window-Functions/td-p/166234

 

  1. ­At some point, can you speak about pass-through queries in SAS and the correct syntax for the connection string?­

Pass-through queries can be used in SAS to send DBMS-specific statements to a DBMS for processing. The syntax for the connection string varies based upon which database you are accessing.  The following introduces SQL pass-through and provides some examples relevant for connecting to Oracle.

http://support.sas.com/documentation/cdl/en/lrcon/69852/HTML/default/viewer.htm#n1kbstf7vw0qcjn1ibfc...

There is also an Ask the Expert session that addresses this topic. It is entitled ‘The What, When, and Why of SAS/ACCESS Interfaces’:

http://support.sas.com/training/askexpert.html

 

  1. ­Is a data view akin to a dashboard?­

These are not equivalent. A view is a type of SAS data set that stores a set of instructions for surfacing the data when the view is used.  Views can be created with SQL or the data step. A dashboard provides views about a particular business process or metric, much more like a report. Please see the following for more information about SQL views:

http://support.sas.com/documentation/cdl/en/lrcon/62955/HTML/default/viewer.htm#a001278914.htm

 

  1. ­What about using proc SQL against Hive or Impala tables? Will this be addressed? ­

For our introductory session, we focus on reading SAS data only. Please see the following for an introduction to reading Hive or Impala tables.

https://support.sas.com/resources/papers/proceedings14/SAS033-2014.pdf

 

  1. Can I use PROC SQL to read a CSV file? If so, what is the syntax?­

The SELECT statement in PROC SQL is appropriate for reading SAS data. It can also connect to a database provided that you have the appropriate SAS/Access engine licensed.

  1. ­Why do you use a QUIT statement when your next step is PROC SQL?­

The QUIT statement is not required when the next statement is PROC SQL. It is included in the demonstration to allow the presenter a modular approach that is flexible for demonstration purposes.

 

  1. ­Is there an equivalent to having a DROP= option on a SET statement in a data step?­

Yes, on the FROM clause and following the name of the data set, you can use the DROP= data set option. Please see the following for more information:

http://support.sas.com/documentation/cdl/en/sqlproc/69822/HTML/default/viewer.htm#n1g7gz9jcpnqi8n1j3...

 

  1. ­Will there be anything on subqueries?

This presentation does not contain a discussion of subqueries. Information on subqueries can be found here:

http://support.sas.com/documentation/cdl/en/sqlproc/69822/HTML/default/viewer.htm#p1st65qbmqdks3n1mc...

 

  1. ­What does the 6 represent in the format=percent6.1?

The 6 indicates that we are asking for 6 total columns to display the value. Anything that requires a position would be included in that number, including decimals and percent signs.

 

  1. ­When adding labels in the select clause, are you still bound by the variable naming rules of SAS?­

No, you are not bound by SAS naming conventions. Labels are designed to be descriptive and can include blanks and other special characters.

 

  1. ­Please list the advantages and disadvantages of PROC SQL over MERGE statement.­

An introduction to this topic is addressed here:

http://www2.sas.com/proceedings/sugi29/269-29.pdf

 

  1. What is difference between WHERE and HAVING?­

A WHERE clause subsets one row at a time. The HAVING clause is used to subset a group of data, possibly after performing some sort of summarization.

  1. ­What does the code else "refund" do?­

The ELSE is used to specify a value that will be used if none of the previous conditions in a CASE expression are met.

 

  1. ­What is the order that the CASE statement is executed? Is it after the FROM clause?

Yes, the FROM clause is executed 1st

 

  1. ­What's best way to get log info from PROC SQL while in display manager?­

You can ­view the log as usual. No special operations are required for PROC SQL­.

 

  1. ­After you create a table using SQL, the table is a temporary table in the WORK library. How can we create the table in a permanent library?

 Assign a permanent library using the LIBNAME statement.  Then, on the CREATE statement in PROC SQL, choose a 2 level name such as the following:

­create table perm.table1 ...

 

 

  1. The presentation mentions horizontal joins. Can SAS SQL also be used for vertical joins?­

Yes, you can use SET operators to accomplish this. The kind of vertical join that we might be most familiar with is the equivalent of appending or concatenating data using the data step.  This can be accomplished with the OUTER UNION CORR operation in PROC SQL.

 

  1. ­Why is the presenter using SQL 89 vs SQL 92? Does SAS have any limitations using SQL 92?­

PROC SQL does closely align with the ANSI SQL-92 standard. If you are referring to the syntax used for the joins, either of the following could be used:

…FROM one, two;

Or

…FROM one INNER JOIN two;

  1. ­What does the "number" option mean on the PROC SQL statement? ­

It is a way for us to request that row numbers be displayed in the output.

 

  1. ­Is it possible to create a variable with sum using a group by and another variable with overall sum in the same SQL step?­

This would likely require 2 SELECT statements in PROC SQL. This might be a time when PROC MEANS/SUMMARY with a CLASS would be a good fit for the task. 

 

  1. Is there a data step equivalent for the SQL FULL JOIN?

The data step process that is most like the FULL JOIN is the data step MERGE. Please be aware that the process by which SQL and the data step join their data is different.  Thus the results will not always be the same between a FULL JOIN and a data step MERGE.

­

  1. ­Is the SAS SQL language the same for z/OS?­

Yes. Since Base SAS includes PROC SQL, it runs on any platform­.

 

  1. ­Is there a web session that covers topics such as HAVING in more detail?­

Currently, there is no session that addresses concepts from this webinar in more detail. We do have a session that addresses reading database data.  It is ‘The What, When and Why or SAS Access Engines’

http://support.sas.com/training/askexpert.html

 

  1. ­Is there a way to outer join two tables and have the resulting output replace one of the original source tables? ­

Yes. On the CREATE statement, use the name of the table that you wish to replace.

 

  1. How do I create a new variable in PROC SQL with the following calculation?

 Newvariable=(0.5 x √[SumX]) + (0.8 x √[SumY]) + (0.6 x (log([Z] +8))) + (0.06 x ([Zx 5])) + 0.32

This expression might be best accommodated by the data step. You will need to use the SQRT function to perform square roots, the * operator for multiplication, the + operator, and the LOG function. 

 

  1. ­Can I use PROC SQL to create a random sample? For instance, PROC SQL can be used to combine data from different tables using a common column like ID. If I only want to keep 150 randomly selected rows from the results set, how could I do this?

This is an example of a task that might be more typically accomplished using the data step. If you would like to use PROC SQL, the following paper addresses this topic:

http://www2.sas.com/proceedings/sugi31/168-31.pdf

 

  1. The previous analyst provided me with a SAS format library to use, but they used SAS 32 bit. This is incompatible with my 64 bit. How can I get PROC SQL to open this?

Please contact the person who sent the information to you in order to resolve this question.

  1. Can you address a little more what the COALESCE function does? Is it needed only when making reports from PROC SQL, or when making tables (datasets) from PROC SQL?

 

It can be used in either situation. For example, it can be used to consolidate all X values into a single column for reporting purposes.  Below are a few helpful links:

http://www.lexjansen.com/pnwsug/2007/how/KirkLafler-UndocumentedandHard-to-findPROCSQLFeatures.pdf

http://blogs.sas.com/content/iml/2011/05/11/the-coalesce-function-proc-sql-compared-with-proc-iml.ht...

 

  1. Can you provide examples that use both the Group By and the Order By?

 

Below is an example from the session that includes an added ORDER BY clause. The GROUP BY indicates the grouping values that are to be used for summarization.  The ORDER BY is used for ordering the results set.  You can think of an ORDER BY as a sorting of the data.

 

  

 

proc sql number;

   select c.providerID, providerName, sum(chgamt) as sumChg, sum(lndiscamt) as sumdisc,

         sum(eeresp) as sumeeresp, sum(paidamt) as sumpaidamt

     from health.claims_sample as c, health.provider as p

     where c.providerID = p.providerID

      group by c.providerID, providerName

      order by providerID

;

quit;

 

 

 

  1. I would like to know more about PROC SQL and the macro language. Are there other things to know besides the INTO clause?

 

Below are some helpful links for further investigating PROC SQL and the macro language:

 

http://support.sas.com/documentation/cdl/en/sqlproc/69822/HTML/default/viewer.htm#p0hwg3z33gllron184...

http://www2.sas.com/proceedings/sugi30/031-30.pdf

 

 

 

Contributors
Your turn
Sign In!

Want to write an article? Sign in with your profile.