Dominique, thank you for an entertaining and educational discussion of one of the greatest philosophical debates of our time (at least for us SAS nerds). I am only saddened that Angelica Schuyler is not invited to the party. She can be my +1. :-)
Ms Weatherspoon - please keep up your work ! I did not know that I can use SAS On demand for FREE !!!! There are so little tutorials on SAS, please share your knowledge. THank you ! Greetings from Poland
Hi Bharath! So glad you found the tutorial helpful! Here are some resources on the SAS Datastep Debugger that might be helpful: 2.sas.com/6059GLY2D and 2.sas.com/6050GLY2E
We are so glad you found the tutorial helpful! Here are some resources on the SAS Datastep Debugger that might be helpful: 2.sas.com/6059GLY2D and 2.sas.com/6050GLY2E
Great presentation Dominique! I was wondering why are you using the retain and keep statement in the DS_MERGE data set. Is the retain statement used to order the variables? Thanks.
Hi Kriss! Great question. Yes, the RETAIN statement is being used to order the variables. The KEEP statement is used to select variables, but not their order. So I included both statements to have the same affect as the SELECT clause in PROC SQL.
Hi Dominique, i have always been asking my self the following question, why is it that so many companies do not encourage sas programmers employees to use Proc sql?
Great presentation. Loved the illustration and coherent organization. My question regards the use case where both input data sets are already SAS data sets, or DATA step syntax is available for an in-database calculation. For these situations, is using SQL with non-sorted data more efficient than pre-sorting data using a PROC SORT on each data step? Or, perhaps, the data are sorted, but on a different key and we don't want to re-sort. I mean, doesn't SQL internally sort if the data is not sorted on the JOIN variables? In asking this question, I am addressing those people who reflexively avoid using the SORT procedure. For the use case where both input data sets are already SAS data sets. Is using SQL with non-sorted data more efficient than pre-sorting data using a PROC SORT on each data step. Or, perhaps, the data are sorted, but on a different key and we don't want to re-sort. I mean, doesn't SQL internally sort if the data is not sorted on the JOIN variables? What about if you have three tables? btw: I used to get through what I thought was a beautiful report, only to be stopped by somebody saying a label or title was wrong. Check the spelling of the word 'diffrent' on one of your TITLE statements.
Hello again, Michael! Thanks for your thought-provoking questions, and also for pointing out the typo in the TITLE statement at time stamp 13:40. We asked SAS instructors about your efficiency questions, and they pointed out that the focus of this video is not efficiency, but a simple overview of the SQL versus DATA step approach. If you're suggesting that all SAS programmers should consider efficiency when deciding on an SQL versus a DATA step solution to a merge/join need, we agree! As you probably already know, a full discussion of efficiency techniques is well outside the scope of what can be covered here in the comments. Our instructors also said when they discuss efficiency in our advanced SAS Training classes, one possible answer to every efficiency question is always "it depends"! As we're sure you know, a diligent SAS programmer should always benchmark different techniques using the actual data or a representative sample of the actual data, and make sure they run the "competing" code on the same machine, with the same memory, and perform benchmarking to decide which method is more efficient before committing to one method over the other. If you have a specific efficiency question on your data for your production work, the best way to get an answer is by working with SAS Technical Support. We're happy to open a support track for you - please send your contact information, site number, and any other details to us at SAS_Cares@sas.com if you'd like us to do that! You're correct that there are reasons a programmer might choose using SQL over DATA step, and you've outlined some of them. If you click "Show More" to see the whole description below the video, you'll find many links to other helpful resources, including: 1) Our on-demand webinar, "What are 10 Ways to Make My SAS Code Run More Efficiently?" 2) A conference paper: "Using DATA Step MERGE and PROC SQL JOIN to Combine SAS® Datasets". This is on 2.sas.com/6050GFvWi, which indexes papers/proceedings from all the SAS regional user group conferences. If you search that site for "efficiency sql", you'll find over 2000 results! A good way to generate some thoughtful discussion on this question would be to open a thread on the SAS Programming forum in our SAS Communities: 2.sas.com/6051GFvWc. Thanks again for your comment and questions, and please let us know if we can help you with anything else!
Hi! When you code a JOIN in SQL and explicitly use "left join" or "right join" the first table you specify in the join clause is the LEFT table and the second table you list in the join clause is the RIGHT table. For a more in-depth class on using PROC SQL for joins, you might be interested in our SQL 1 class: 2.sas.com/6056KvsFa ; for more in-depth coverage of using a DATA step MERGE, you might be interested in our Programming 2 class: 2.sas.com/6057KvsFx . Hope that information helps!
Circling back with you, Lauren! Your SAS data set is already in a table structure of rows and columns, so we're not exactly sure what you mean when you ask "how to make tables from my data set so I can then combine my tables to create one large table". If you already have a SAS table, then you would use either PROC SQL or the SAS DATA step program to combine your tables. You can combine your tables vertically, such as making a QUARTER1 table or dataset from the JANSALES, FEBSALES and MARSALES individual tables. Or you can combine your table horizontally, such as joining or merging an EMPLOYEE master file with an ORDER file to get a final table that shows which employees worked on which orders. Without knowing more about your SAS background and your data and how you envision joining the tables together, it's hard to point you to resources. The best reference would be the combination of our Programming 1 and Programming 2 classes. This SAS Community Forum posting by one of our instructors illustrates both the SET and MERGE for joining tables, with screen shots: 2.sas.com/6052H1bNM look at the 5-19-2018 posting. If you have specific questions with your data and how to combine the tables, you can send your question to Tech Support by filling out the form at this link: 2.sas.com/6053H1bN3 . Or, for some suggestions from other SAS users on links they have found useful, you might consider posting your question in the SAS Community Forum for SAS Programming questions: 2.sas.com/6054H1bNO . Happy programming!
After researching further, see if this code looks helpful: PROC SQL creates one table at a time. proc sql; create table CarsAsia as select DriveTrain, count(*) as Number, mean(MSRP) as AvgMSRP format=comma10. from 2.sas.com/6054GAHb8 where Origin='Asia' group by DriveTrain ; create table CarsEurope as select Type, count(*) as Number, mean(Invoice) as AvgInvoice format=comma10. from 2.sas.com/6054GAHb8 where Origin='Europe' group by Type ; create table CarsUSA as select * from 2.sas.com/6054GAHb8 where Origin='USA' ; quit;
If you are indicating, to mean which statement in SQL is equivalent to the IF/THEN/ELSE conditional logic in the DATA step. In which case it would bet SQL CASE Expression. Here is a link to the documentation that might be helpful to the customer from the SAS SQL Procedure User's Guide: Case Expression
Thanks for your patience while we checked with our Training team on this! We do not provide the data for our tutorials. Most of our tutorial videos use data that the instructor created for the video or got from the documentation samples, used from SASHELP library, or used from one of our classes. If you take our free Programming 1 class, you would learn how to create your own test datasets such as the ones the instructor is demoing in the video. Our intent with these free tutorials is for students to watch the free tutorials and then use their own data. The SASHELP data library, which is part of every SAS installation contains over 200 datasets that you can use to practice. Here's a useful document that describes the SASHELP data 2.sas.com/6051GxiLt. Or, you look in the SAS documentation for sample data. For example, on the site for this PROC SQL example: 2.sas.com/6052GxiLQ and you will find a link to download the programs that make the STAFF file and the PAYROLL file to practice the join in the example. In addition, if you activate the free learning subscription (link below the video), you would have 30 days to practice with our SQL 1 class and in the class you would find the program that makes the data we use to teach the class. You could practice with that data. We hope that information helps!
Hey everyone! Thank you for watching! Leave a comment if you have any questions :)
Gotta love the energy you gave for this video!
We appreciate the feedback and hope you enjoyed!
Can you please make a video on proc template.
I'm trying to convert a .SQL file to SAS. How can I do it?
Thanks for your question, Qismatkamara. We're checking on it, and will get back to you soon!
I love the energy. You made a stale topic feel fresh. More videos, please!!!
Awesome! Thanks so much for the feedback!
Dominique, thank you for an entertaining and educational discussion of one of the greatest philosophical debates of our time (at least for us SAS nerds). I am only saddened that Angelica Schuyler is not invited to the party. She can be my +1. :-)
Haha I totally agree. It's an error in the data :)
Ms Weatherspoon - please keep up your work ! I did not know that I can use SAS On demand for FREE !!!! There are so little tutorials on SAS, please share your knowledge. THank you ! Greetings from Poland
We do offer some free e-learning and tutorials to help you #LearnSAS Register for our SAS Starter Kit to find out more information 2.sas.com/6053MkGsv
Fantastic as the topic is explained.
We're glad you enjoyed it!
Nice Explanation
Glad you liked the video! 👍
Best explanation ever found so far
Beautiful teacher
Thanks you so much
From germany
You're very welcome! We're so glad you enjoyed the content!
Awesome explanation 🙂
Glad it was helpful!
Great explanation!
So glad you found it helpful!
Hi Dominique,
It was a worth watching presentation and refreshed my basics. Can you please share the more details about SAS Datastep Debugger.
Hi Bharath! So glad you found the tutorial helpful! Here are some resources on the SAS Datastep Debugger that might be helpful: 2.sas.com/6059GLY2D and 2.sas.com/6050GLY2E
I didn't know there was a data step debugger! Too bad I don't use IG!
The DATA step debugger is the best! Besides being in SAS Enterprise Guide, it's also in SAS Studio for Viya (version 5.2 or later).
love the energy
Very well explained..
Would like to know more about datastep debugger
We are so glad you found the tutorial helpful! Here are some resources on the SAS Datastep Debugger that might be helpful: 2.sas.com/6059GLY2D and 2.sas.com/6050GLY2E
Great presentation Dominique! I was wondering why are you using the retain and keep statement in the DS_MERGE data set. Is the retain statement used to order the variables? Thanks.
Hi Kriss! Great question. Yes, the RETAIN statement is being used to order the variables. The KEEP statement is used to select variables, but not their order. So I included both statements to have the same affect as the SELECT clause in PROC SQL.
@@dominiqueweatherspoon473 Great, thank you for answering this!
🌟 awesome
Thanks 🤗
Hi Dominique, i have always been asking my self the following question, why is it that so many companies do not encourage sas programmers employees to use Proc sql?
Thanks for asking! It's hard to know for sure why an employer might do that. In some cases it could be that they are not familiar with it.
Great presentation. Loved the illustration and coherent organization.
My question regards the use case where both input data sets are already SAS data sets, or DATA step syntax is available for an in-database calculation. For these situations, is using SQL with non-sorted data more efficient than pre-sorting data using a PROC SORT on each data step? Or, perhaps, the data are sorted, but on a different key and we don't want to re-sort. I mean, doesn't SQL internally sort if the data is not sorted on the JOIN variables? In asking this question, I am addressing those people who reflexively avoid using the SORT procedure.
For the use case where both input data sets are already SAS data sets. Is using SQL with non-sorted data more efficient than pre-sorting data using a PROC SORT on each data step. Or, perhaps, the data are sorted, but on a different key and we don't want to re-sort. I mean, doesn't SQL internally sort if the data is not sorted on the JOIN variables?
What about if you have three tables?
btw: I used to get through what I thought was a beautiful report, only to be stopped by somebody saying a label or title was wrong. Check the spelling of the word 'diffrent' on one of your TITLE statements.
Thanks for your comments, Michael! We're checking on your questions, and will get back to you soon!
Hello again, Michael!
Thanks for your thought-provoking questions, and also for pointing out the typo in the TITLE statement at time stamp 13:40.
We asked SAS instructors about your efficiency questions, and they pointed out that the focus of this video is not efficiency, but a simple overview of the SQL versus DATA step approach. If you're suggesting that all SAS programmers should consider efficiency when deciding on an SQL versus a DATA step solution to a merge/join need, we agree! As you probably already know, a full discussion of efficiency techniques is well outside the scope of what can be covered here in the comments.
Our instructors also said when they discuss efficiency in our advanced SAS Training classes, one possible answer to every efficiency question is always "it depends"! As we're sure you know, a diligent SAS programmer should always benchmark different techniques using the actual data or a representative sample of the actual data, and make sure they run the "competing" code on the same machine, with the same memory, and perform benchmarking to decide which method is more efficient before committing to one method over the other. If you have a specific efficiency question on your data for your production work, the best way to get an answer is by working with SAS Technical Support. We're happy to open a support track for you - please send your contact information, site number, and any other details to us at SAS_Cares@sas.com if you'd like us to do that!
You're correct that there are reasons a programmer might choose using SQL over DATA step, and you've outlined some of them. If you click "Show More" to see the whole description below the video, you'll find many links to other helpful resources, including:
1) Our on-demand webinar, "What are 10 Ways to Make My SAS Code Run More Efficiently?"
2) A conference paper: "Using DATA Step MERGE and PROC SQL JOIN to Combine SAS® Datasets". This is on 2.sas.com/6050GFvWi, which indexes papers/proceedings from all the SAS regional user group conferences. If you search that site for "efficiency sql", you'll find over 2000 results!
A good way to generate some thoughtful discussion on this question would be to open a thread on the SAS Programming forum in our SAS Communities: 2.sas.com/6051GFvWc.
Thanks again for your comment and questions, and please let us know if we can help you with anything else!
Excellent
Is what you call right or left table relative to how see in the screen.
Hi Somcana! We're checking on this for you, and will follow back up with more info! 👍
Hi!
When you code a JOIN in SQL and explicitly use "left join" or "right join" the first table you specify in the join clause is the LEFT table and the second table you list in the join clause is the RIGHT table. For a more in-depth class on using PROC SQL for joins, you might be interested in our SQL 1 class: 2.sas.com/6056KvsFa ; for more in-depth coverage of using a DATA step MERGE, you might be interested in our Programming 2 class: 2.sas.com/6057KvsFx .
Hope that information helps!
👏👏
Hi Dominique, can you send a link that shows me how to make tables from my data set so I can then combine my tables to create one large table? Thanks!
Checking on this for you!
Circling back with you, Lauren! Your SAS data set is already in a table structure of rows and columns, so we're not exactly sure what you mean when you ask "how to make tables from my data set so I can then combine my tables to create one large table". If you already have a SAS table, then you would use either PROC SQL or the SAS DATA step program to combine your tables. You can combine your tables vertically, such as making a QUARTER1 table or dataset from the JANSALES, FEBSALES and MARSALES individual tables. Or you can combine your table horizontally, such as joining or merging an EMPLOYEE master file with an ORDER file to get a final table that shows which employees worked on which orders.
Without knowing more about your SAS background and your data and how you envision joining the tables together, it's hard to point you to resources. The best reference would be the combination of our Programming 1 and Programming 2 classes. This SAS Community Forum posting by one of our instructors illustrates both the SET and MERGE for joining tables, with screen shots: 2.sas.com/6052H1bNM look at the 5-19-2018 posting.
If you have specific questions with your data and how to combine the tables, you can send your question to Tech Support by filling out the form at this link: 2.sas.com/6053H1bN3 . Or, for some suggestions from other SAS users on links they have found useful, you might consider posting your question in the SAS Community Forum for SAS Programming questions: 2.sas.com/6054H1bNO .
Happy programming!
can proc sql output multiple datasets just like data step or it can just create one table at a time? Thanks!
Thank you for your inquiry! We are checking on this for you!
After researching further, see if this code looks helpful: PROC SQL creates one table at a time.
proc sql;
create table CarsAsia as
select DriveTrain, count(*) as Number,
mean(MSRP) as AvgMSRP format=comma10.
from 2.sas.com/6054GAHb8
where Origin='Asia'
group by DriveTrain
;
create table CarsEurope as
select Type, count(*) as Number,
mean(Invoice) as AvgInvoice format=comma10.
from 2.sas.com/6054GAHb8
where Origin='Europe'
group by Type
;
create table CarsUSA as
select * from 2.sas.com/6054GAHb8
where Origin='USA'
;
quit;
@@SASUsers neat, didn't know you can create multiple tables within one query
"ELse" statement in SAS Similar to which statement in Proc SQL?
Srinu, thank you for your inquiry! We are checking on this for you!
If you are indicating, to mean which statement in SQL is equivalent to the IF/THEN/ELSE conditional logic in the DATA step. In which case it would bet SQL CASE Expression. Here is a link to the documentation that might be helpful to the customer from the SAS SQL Procedure User's Guide: Case Expression
@@SASUsers Thank you for your Prompt Response.
Could we have the data you use in the video? thank you so much.
Hi there! We're looking into this for you and will get back to you!
Thanks for your patience while we checked with our Training team on this! We do not provide the data for our tutorials. Most of our tutorial videos use data that the instructor created for the video or got from the documentation samples, used from SASHELP library, or used from one of our classes. If you take our free Programming 1 class, you would learn how to create your own test datasets such as the ones the instructor is demoing in the video.
Our intent with these free tutorials is for students to watch the free tutorials and then use their own data. The SASHELP data library, which is part of every SAS installation contains over 200 datasets that you can use to practice. Here's a useful document that describes the SASHELP data 2.sas.com/6051GxiLt. Or, you look in the SAS documentation for sample data. For example, on the site for this PROC SQL example: 2.sas.com/6052GxiLQ and you will find a link to download the programs that make the STAFF file and the PAYROLL file to practice the join in the example.
In addition, if you activate the free learning subscription (link below the video), you would have 30 days to practice with our SQL 1 class and in the class you would find the program that makes the data we use to teach the class. You could practice with that data.
We hope that information helps!
Please advice dataset debugger I am a beiginner
Hi! Let us know if this helps: 2.sas.com/6055JerTB