BookmarkSubscribeRSS Feed

Questions and Answers from SAS Certification Webinar: Get Prepared: SAS Advanced Programming Professional Exam June 25, 2020

Started ‎07-02-2020 by
Modified ‎09-21-2020 by
Views 1,941

Q: Will this webinar be recorded and made available later?  When will this webinar be offered again?

A: Yes. An on-demand version of the webcast will be available within a few days and can be accessed using the same audience link that was sent to you earlier when you registered for the webinar.  The Q&A will be posted to the Certification community.  We will also make the examples shown in the webinar available along with the associated files within a few days of the webinar.  Check the SAS Certification Community board for information and links to Q&A.


Q: Do I need to get SAS-Certified base Programmer before I register SAS-Certified Advanced Programmer? 

A: Yes, you need to have the Base Programming credential before you can receive credit for the Advanced credential.


Q: Is it the case that I cannot take the practice exam on a Mac desktop?

A: Practice exams could be taken on a Mac. Also, as of Sept 21, 2020 - All SAS exams may be taken on a Mac.


Q: Could you please provide the link for training exam?

A: Here is the link to the Advanced Programming credential and exam.


Q: Could you please provide more information about the free Advanced Programming Professional practice exam?  Where can I find it? 

A:  You can access the free practice exam through the following link:


Q: Why is the presenter using Enterprise Guide for this presentation?  Do we need to be familiar with SAS EG to take the exam?

A: The presenter just happens to be using Enterprise Guide.  On the performance-based exam, you can use SAS Studio, Enterprise Guide or SAS Display Manager.


Q: Is there a webinar also for the A00-231 Base Programming Specialist Exam?

A: Yes, there is a webinar for that exam as well. You can see all of our free webinars at this location:  then click on the Base Programming icon.


Q: Is the delta exam for Advanced Programming only for existing users or for everyone?

A: The delta exam is only for a person who has the old SAS Advanced Programmer for SAS 9 credential. If you have that credential, you can take this delta exam to upgrade your credential to the Advanced Programming Professional.


Q: I have a question about the wording on the exam. For example, PROC SQL JOIN versus SET operator. Does the exam question state which you should use JOIN or SET operator?

A: It is possible that an exam question would specify a technique to use. If it does, you will need to use that specific technique.


Q: Is the subquery really providing a dynamic list?

A: Yes, it is.


Q: I heard the speaker mention efficiency and was curious about the sub-query's efficiency over a join?

A: From a systems standpoint, joins are usually more efficient overall than subqueries, because the optimizer can optimize the entire query. Subqueries must be optimized separately from the outer query. Additionally, please keep in mind that I stated that a subquery is more efficient in the presented example because the outer did not require data from the second table.  We used the subquery to return dynamic values for the WHERE clause.


Q: Except / intersect could be replaced by IN /NOT subqueries - which is better?

A: Generally, the IN operator is more efficient than the SQL SET operators. But if you are concerned about efficiency, you will have to benchmark to answer that question for your specific program.


Q: if the hash table is excellent with large tables, but only used in memory, what happens if the table is too large to be kept in memory?  Can you still use the hash table?

A: If you do not have enough memory to load the hash table, consider using a DATA step merge or SQL join instead.

The DATA step with the hash object will fail with an error something like this if there is insufficient memory:

FATAL: Insufficient memory to execute DATA step program.
ERROR: The SAS System stopped processing this step because of insufficient memory.


Q: What is the difference between merge and hash?   Because we can merge the two datasets.

A: Merge requires the tables to be pre-sorted and a hash table lookup does not.


Q: I have a question for the last speaker with hash objects. I have been studying them. In the last method, 2 program can you add another line for output where adopt.find() ne 0; thanks.?

A:  Yes, you could do that.


Q: Could you talk a little bit about PROC DS2 and its use for data management?

A: The speaker answered this question in the webinar and provides some additional information:

See also:
SAS® Data Management: Technology Options for Ensuring a Quality Journey Through the Data Management ...

And the DS2 Procedure in the SAS docs.


Q: The four operators (used in the PROC SQL example) are "UNION" "OUTER UNION" "EXCEPT" and "INTERSECTION" correct?



Q: For hash objects, the DEFINEKEY which we are going to use should be of the same data type or will it work with different data type as well?

A: You can define one or more keys in a hash object. The key components can be character, numeric or both and must be defined as variables in the PDV.


Q: In Theresa’s first code example, she pointed out her preference of using a subquery as an efficiency tip over a join. Question under-the-covers, doesn’t SQL convert a sub-query to a join anyway if the optimizer deems it necessary?

A: No. You can add the _METHOD _TREE option to the POC SQL statement to see how the query executes. For more information about _METHOD and _TREE,


Q: Would you recommend a hash object search/lookup for a table that is 300K rows and the lookup table is 3 billion rows? I ask before I often get Sort Execution failure errors?

A: The recommendation would be to try an SQL JOIN.


Q: Can the hash table persist from data step to data step?

A: No, it cannot.


Q: Do you always load the smaller table in memory?

A: The most limiting constraint for hash objects is usually memory, so it makes sense to load the smaller table into the hash.


Q: Could you please talk a little about PROC FCMP?

A: Please see:  Using PROC FCMP to the Fullest: Getting Started and Doing More by Art Carpenter.


Q: Can we put more than one KEY in hash key?

A: Yes. Please see:  DEFINE KEY in the SAS docs for more info.


Q: When working with small tables as in your examples, as far as efficiency goes, does it matter which is used merge or hash?   Is there a threshold size of table when it matters?

A: If efficiency matters, benchmark!  It’s also important to specify which resource you are attempting to conserve when you say, “more efficient”.

For example, a quick non-scientific comparison using small tables, the hash was faster (lower real / elapsed time) and the merge used less CPU.


Here is the link to the Library article on the SAS Community site that provides great tips and strategies for taking the Advanced Programming Professional Exam:


Version history
Last update:
‎09-21-2020 08:56 AM
Updated by:



Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

Free course: Data Literacy Essentials

Data Literacy is for all, even absolute beginners. Jump on board with this free e-learning  and boost your career prospects.

Get Started

Article Tags