Why Proc SQL when everything can be done with SAS Base?
Someone with a year to two years of experience is more likely to ask this question.
First of all, I agree that SAS Base is a complete data analysis tool and in my opinion, its one of the most efficient tools available in the market to handle large volumes of data with excellent processing speed. Also, no doubt that SAS is a winner in advanced statistical analysis like Predictive Analytics.
So why Proc SQL?
If you know only these few things in SQL, you would never ever need to look a those complex merges and daunting procs in SAS Base.In this post I will only write Why and not How. So you will have to search these methods and get yourself familiar with them.
Joins: Left, Right and Inner
Very easy to understand. You will find lot of videos on youtube explaining different types of joins in SQL. Why Joins and Not Merges - You don't need to sort your tables before joining and 'By Variable' doesn't need to have same name, so no renaming of variables.
Proc SQL summary functions: SUM/MEAN/AVG
Many times you have to sum-up data in SAS and to do that you have to use Proc Means. Proc Means comes with complex procedure and output format. I don't like the format of the tables that you get with the Output Out = statement.
With Proc SQL its very easy to summarize your data with help of Group by Statement. Best source is Youtube to learn Group By statement in SQL.
Simple Sub-queries in SQL are very handy. Don't go too complex.
Suppose you want to select everything from table A where there are no matching records in table B. In SAS, again get all things correct first like same variable name, sorting all tables and then merge them. In Proc SQL, this simple statement will work:
select * from A where Name not in (Select Name from B);
No Sorting, No rename of variables.
Again, you will find lot of video on SQL Joins, Group By Statement and Sub-Queries in SQL on youtube. Most of the SQL code works in Proc SQL.
Someone with a year to two years of experience is more likely to ask this question.
First of all, I agree that SAS Base is a complete data analysis tool and in my opinion, its one of the most efficient tools available in the market to handle large volumes of data with excellent processing speed. Also, no doubt that SAS is a winner in advanced statistical analysis like Predictive Analytics.
So why Proc SQL?
If you know only these few things in SQL, you would never ever need to look a those complex merges and daunting procs in SAS Base.In this post I will only write Why and not How. So you will have to search these methods and get yourself familiar with them.
Joins: Left, Right and Inner
Very easy to understand. You will find lot of videos on youtube explaining different types of joins in SQL. Why Joins and Not Merges - You don't need to sort your tables before joining and 'By Variable' doesn't need to have same name, so no renaming of variables.
Proc SQL summary functions: SUM/MEAN/AVG
Many times you have to sum-up data in SAS and to do that you have to use Proc Means. Proc Means comes with complex procedure and output format. I don't like the format of the tables that you get with the Output Out = statement.
With Proc SQL its very easy to summarize your data with help of Group by Statement. Best source is Youtube to learn Group By statement in SQL.
Simple Sub-queries in SQL are very handy. Don't go too complex.
Suppose you want to select everything from table A where there are no matching records in table B. In SAS, again get all things correct first like same variable name, sorting all tables and then merge them. In Proc SQL, this simple statement will work:
select * from A where Name not in (Select Name from B);
No Sorting, No rename of variables.
Again, you will find lot of video on SQL Joins, Group By Statement and Sub-Queries in SQL on youtube. Most of the SQL code works in Proc SQL.
ReplyDeleteThanks for sharing this valuable post to my knowledge great pleasure to be here SAS has great scope in IT industry. It’s an application suite that can change, manage & retrieve data from the variety of origin & perform statistical analytic on it
Regards,
sas training in Chennai|sas course in Chennai
Well Said, you have furnished the right information that will be useful to anyone at all time. Thanks for sharing your Ideas.
ReplyDeleteAngularjs training in chennai | Angularjs course in Chennai