PROC SQL in SAS | PROC SQL All in One | A Complete Guide to Proc SQL in SAS
Vložit
- čas přidán 15. 01. 2021
- After learning the SAS Base it is important to take your skills to next level and this is where this video helps you learn the PROC SQL from basic to advance level.
This video covers the following topics:
► What is PROC SQL in SAS
► How to write the PROC SQL statement/statements
► What is SELECT and FROM statement in PROC SQL
► What comes in SELECT and what comes in FROM when you write a PROC SQL statement in SAS
► Selection all/one/multiple columns from a table using SELECT and FROM CLAUSE
► Selecting based on conditions. Using WHERE clause in PROC SQL to filter the data
► Summarizing the data of a table based on some specific variables. Using GROUP BY clause in PROC SQL to summarize the data
► Filter the data post summarization in PROC SQL. Filter on the variable calculated in the query. Using HAVING clause in PROC SQL to filter the data
► Sorting in PROC SQL. Using ORDER BY clause in PROC SQL.
► Introduction to CASE WHEN statement
► Simple use of Case When to Create a Variable/Column Based on One Variable
► Multiple Case When or Nested Case When to Create a Variable/Column using one Variable or Column
► Using Case When Statement on More Than One Variable to Create a Column or Variable
► Introduction to PROC SQL
► List of the Summary Functions Used in PROC SQL
► COUNT, FREQ, N to Take the Count of Non-Missing Observations
► MIN to Find the Minimum Value in a Given Numeric Variable
► MAX to Find the Maximum Value in a Given Numeric Variable
► AVG, MEAN to Find the Average/Mean Value of a Given Variable
► RANGE to Find the Range of a Category on Given Numeric Variable
► STD to Find the Standard Deviation of a Category on Given Numeric Variable
► SUM to Find the Total by a Category of a Given Numeric Variable
► Grouping on More Than One Variable or Summarizing on More Than One Variable
► Introduction to Joins in PROC SQL in SAS
► Type of Joins Used in PROC SQL SAS
► LEFT Join to Merge/Join Two/More Tables in PROC SQL in SAS
► RIGHT Join to Merge/Join Two/More Tables in PROC SQL in SAS
► INNER Join to Merge/Join Two/More Tables in PROC SQL in SAS
► FULL Join to Merge/Join Two/More Tables in PROC SQL in SAS
► Summary or an Overview of All The Joins in PROC SQL in SAS
► Introduction to Joins in PROC SQL in SAS
► Type of Joins Used in PROC SQL SAS
► LEFT Join to Merge/Join Two/More Tables in PROC SQL in SAS
► RIGHT Join to Merge/Join Two/More Tables in PROC SQL in SAS
► INNER Join to Merge/Join Two/More Tables in PROC SQL in SAS
► FULL Join to Merge/Join Two/More Tables in PROC SQL in SAS
► Summary or an Overview of All The Joins in PROC SQL in SAS
► Prerequisites to Merge or Join the Tables in SAS
► LEFT Join VS Merge in SAS
► RIGHT Join VS Merge in SAS
► INNER Join VS Merge in SAS
► FULL Join VS Merge in SAS
#Learnerea
#SASTraining #SASTutorial #SASCourse #SASTutorialForBeginners #SAS #SASforBeginners
#SASProgramming #SASTProgrammingForBeginners #SASProgrammingTutorial
Contacts:
Facebook ► / learnerea
Download the excel files for practice data
GitHub ► github.com/LEARNEREA/Excel_Files - Věda a technologie
just what I needed. Thank you. Much love
This has been the most comprehensive as well as of short duration video. I have learned so so much. Thank you!
Kudos to the instructor. All other SAS tutorials are so confusing and boring but this one is really good!!!
Glad it helped
YOU METHOD IS SO CREATIVE SIR, THANKS
Great video👍,very very helpful! I’ve learn a lot from this tutorial. Thanks! 🌹🌹
The best of what I have watched about SQL in the simplest fashion so far. Thank you
Glad you liked it
Great job 👏 ..thank you..
very well explained..thanks a lot
Thnku very much sir for giving good info .. really I learnt a lot.. keep make videos sir
Very nice job!
excellent video.
Thanks for the wonderful content video in proc SQL. Kudos to your efforts taken.
Glad you liked it!
Very easy to follow and much helpful! Thanks!
Glad it was helpful!
Wow, great job, I am understanding sas now, thank you.
Glad it was helpful!
This was fantastic. Thank you very much.
Thankyou for your kind words, happy learning
You are genius
Such a great video! I l learned a lot within a short time.
Glad it was helpful!
Big thanks for the tutoring!
Hope this was helpful to you in someway
Awesome effort sir.... your teaching explanation is too good
So nice of you
Thanks man
Very good explainer, good pedagogy.
I'm glad you liked it 😊
thank you
thanks, its nice nice video for proc sql
Glad you like it
Thank you very much, it a very informative video.
Glad it helped you
Amazing video
Glad it was helpful
Very well explained 👍👍👍👍
Thanks for liking
Thank you very much
You are welcome
Thank you 🙏
Glad you liked it
So helpful
Glad it was helpful!
Plz uploaded some thing about in clinical trials
Bro, will u please INSERT INTO , ALTER, UPDATE also in PROC SQL , aren't they important in realtime as a SAS Coder.
Please start sdtm videos and tutorial
will try and do it soon
Nice video with one item noted: when the output changes at 31:24, we observe a category value of "toddler" that shows up with no prior code to tag the age category as "toddler". All we had before this was either "kid", "teenage" or "young", based on age. Where is the code to tag "toddler"? Oops.
Alex thanks for highlighting this, we are definitely going to be careful about such mismatches however here the category 'Kids' can be considered as toddler. To note this has nothing to do real life toddler age 😊
Please create a series of table listing figure
Will do
Do you have any video about PROC ARRAYS and PROC SURVEYSELECT?
Hi Ajith,
We have the video for Arrays which you can watch using below link and for SURVEYSELECT we will make the one soon. Happy Learning -
czcams.com/video/3j-UKFrv3tc/video.html
Thanks for your videos sir.It helped me a lot.Cant we use IF condition instead of WHERE in proc SQL sir...
Hi Rajesh,
In PROC SQL one can use where clause and having and not the if statement.
'If' condition cannot be used in Procedure i.e. 'Proc' step.
IF then else are used only in data step
@@InsideAnkitasUniverse we can use if condition in one procedure i.e proc report with compute statement
Do you have different types of logic and answers I can use to run and learn? Please! either with Cars or any data set.
check out our newly uploaded videos like - calculating the rolling average... you will get the logical ideas...
great video, thank you so much! Have a question, in sql, we could use LIMIT to select top n observations or rows for a certain column, let's say select top 5 records with math score in each grade, how we get this with sas sql? or we can use this car data, to select top 5 most expensive cars in each car type? In sql, you can use group by type order by msrp, then limit 5, but sas proc sql doesn't have LIMIT function, how we do it in SAS proc sql?
you can use the option "outobs" but that will give you n number of records on overall level and not by each of the group
if you are interested in doing that using data step then what you can do is -
first - sort the data by car type and msrp in descending order
second - create a counter variable by car type (to learn this you can check out the video - czcams.com/video/8FDkHul0TL0/video.html )
third - filter for the rows where counter variable is
amboxj hay azge qo heta, shat enq qez sirum u misht hetevum enq qez
Thanks for your video! it's very helpful! i went to the link and try to download file. could you advise me where can i find the excel file for cars?
You can find cars dataset in sashelp library
That is not an excel file, we cannot read excel file in sas, only sas dataset with extension sas.7bdat can only be accessed on sas interface
IN JOINS WHEN THERE ARE COMMON OBSERVATIONS OF MORE THAN 1 IN ON VARIABLES WE WILL GET THE CARTESIAN PRODUCT.
HOW TO HANDLE THAT SITUATION
If have this in the column you are using as key to join the tables, if so then either-
>> please remove the duplicates first and then join
>> Concatenate that column with any other one which is common in both and doesn't have duplicates, that will create unique values and then you can use the same as key var. to join further
@@learnerea YEAH TQ
Could you pls tell I am using Sum function to get the amount of gross amount for previous month..but I got double amount on same..
can you check your group by statement.. better you put your code here
Can you please tell Group by and order by difference
Order by is to sort your data either in ascending or descending order while group by is for aggregation of the data with a calculation
can you explain how to find second highest value in proc sql
for that you can try to create counter using first. in data step
Can you make example, on what kind of occasion we use full join?
Maybe, an exhaustive list of all the customers from customer table and trans table for a given month
@@learnerea well，very rare ,but thanks
@@happypotato9644 yeah that's true, not used oftenly
pls tell the how to remove the duplicate in proc sql
try using, select distinct
Where are the links your taking about in video?
Not sure what link you are exactly looking for. In the description section, the github link for all the excel files is given, same mentioned here againg -
github.com/LEARNEREA/Excel_Files
at the below github link, you can find all of the SAS Scripts and Data used -
github.com/LEARNEREA/SAS
From below, you can download many of the datasets stored in excel -
github.com/LEARNEREA/Excel_Files
let me know in case of any further suggestions needed
How to find Commulative Sum Or Running total Through Proc SQL ?
Hi Suman,
There are certain limitations with Proc SQL as well as with Data Step and that is where we use either of them as an alternate. Therefore you can use data step to calculate the cumulative sum
is this content helpful for clinical SAS ?
Yes it will help
How to define sex variable as f = female and m= male in proc SQL program what is the code for that
case when sex = "f" then "female" else "male" end as sex_new_var
you can check out below for detailed explanations -
czcams.com/video/5v-wtE6dJNU/video.html
super
macros please
Hi Ashok,
Here comes what you have been waiting for long:
czcams.com/video/i4hu1b9r_Qw/video.html
@@learnerea Thank you bro
You teached full join out put wrong
We can discuss the descripancy, please feel free to share it in detail