Welcome to clinical data management program using SAS. In this video we will be discussing about SQL SAS essentials. So at first the data set that we are going to use, we have to bring the data set in the SAS environment. So for that we have to create the live name statement, select name library name is CBN, then we need to give the pack so here, we copy the path. This is our path includes the double quotes and then we give semicolon, so let's run this code. So these are my data sets.

Now let's start with proc SQL. So our procedure starts with the statement, proc SQL select star, which stands for that is, which stands for selecting all the variables that is we want to display all the variables in our result view. So that's what we're doing select star asterisk stands for all the variables from CDM is my library. name dot disease semicolon and then quit. Let's run this code. over here to this is my disease data set which consists of the variables like ID gender, date of birth zip code, a Brahmin status, education, marital status, children ancestry, average commute, daily internet use available services, military service and disease, and it consists of around 2000 observations.

So over here when we do progress can select Start means we want to display all the variables in our viewer from CGM for disease, this is our source table and then quit. So this is our result viewer. We have to give the non title statement to nullify the title. So we can give title and semicolon to modify the data See now the data has gone. So all the variables and all the observations are displayed over here because we haven't applied any filtering or any other modification. So, all observations with all the variables are displayed next.

Now, suppose we want to apply some filter to our table that is we want to display only those observations where marital status is equals to married an average commute is greater than 30. So how will we proceed using the same procedure proc SQL Select star from CDM doc disease with marital status Equals married and average commute is greater than 30 and then quit. So only those observations with a marital status is married. And average commute greater than 30 days this is the average commute variable is displayed. So I've just written 30 and marital status equals to married only those observations are displayed, which fulfills these two criteria such as marital status equals to married an average commute greater than 30. Next, suppose we after applying the filter on the data as marital status equals to married or average commute, greater than 30.

We also want to use one we want to sorta result in table by daily turret using descending order. So how do we proceed? So for this we are going to use proc SQL select star. That is we want to display all the variables from CG dot disease Vish merited status equals married this commute waited all day by day didn't use descending. So we give the keyword is de sc. That is we want to sort our resulting table with respect to the variable data introduce in descending order and then quit so let's run this code SR Dale introduces sorted in descending order.

All observations the average commute greater than 30 and marital status equals to monitor displayed. Next, suppose we want good displayed the variables or disease and we want to sum up average commute for each disease and then we are going to want to order a certain table with respect to disease, then how do we proceed so we'll be using proc SQL we want the sum of average compute for each disease. So basically we have to group the data with respect to disease so we'll be using the GROUP BY clause over here. So first let's give the variables that you specify in SELECT statement we want the variable as disease to come. We want to sum of average commute as some from CDM doc disease grew by disease Having disease equals to skin cancer and then we are doing order by disease. Then basically over here we want to display the variables disease and you own the sum of the average commute for each disease and we want to display only those diseases for skin cancer.

So, after we group we are applying the having clause. That means on the group data this filter will be applied so only the disease for skin cancer will be displayed. So the sum of average commute only for the diseases cancer will be displayed. So see only the disease for skin cancer and the sum of average communities district For the last case we were not getting because there was a syntax error as we gave sericata for each and every step. So, just we have removed the semicolon. So we got the proper output.

Now suppose we want the sum of average commute for each and every disease not only for skin cancer, but for each and every disease. So how do we proceed? slay proc SQL select disease, coma some average commute some from CDM dot disease, group disease and then audible disease and then quit. So let's run this code to see for each of the diseases that some of our risk communities displayed for Alzheimer's disease for HIV for breast cancer, diabetes, endometriosis, gastritis, heart disease, hypertension and kidney disease, multiple sclerosis, prostate cancer, schizophrenia, skin cancer and etc. Now suppose that after playing the group tour filter data that is we have the table where we are getting the sum of average commute for the disease can cancel but we want a filter applied to this table that is we want the sum to be calculated only for those observations where my employment status is returned.

That means I want to display the sum of average commute of the disease was suffering from skin cancer, provided their employment status is equals to two Retired that is they are retired people or their employment status is retired. How do we proceed with it we'll be using again progress keyword select disease, comma PSA average commute as someone from CDM dot disease there. Now here we are applying the filter. This is not the end of our query. So we cannot leave semicolon where employment status equals retired. group by disease having disease equals to skin cancer ordered by disease and then quick so let's run this code here here.

We want to display those variables like disease. Some of average computer someone for the disease's skin cancer. It is grouping with respect to disease and the data is filtered. There is employment status has to be equal to retire. So let's run this code. To see here, the skin cancer patients the sum of average commute is displayed, provided their employment status is equal to retired.

So only for those patients are displayed where their employment status is recorded or retired. Previously, all the patients were displayed that's where my son was born. Now see the summons reduced, because I have also added one folder that is employment status has to be retired. The next suppose I want to display 50 observations from my data. So how will it do that profit scale out tops equals to 50 that is it will take the first 50 observations, then select Select star from CDM doc disease then quit so let's run this code only 50 observations are displayed from our data out of sequence 250 mins it limits the number of observations only 50 observations are displayed. Similarly suppose we want to display only unique diseases that is we do not want the repetitions of disease names we want the unique values of the variable disease.

So how do we proceed we'll do progress keyword, semicolon, select distinct disease that is unique values of disease disease without duplicates will be displayed from CDM dot disease semicolon and then let's run this code to see only the unique names of the diseases without their deeply in repetitions or duplicates a dispute. So there are these are the different diseases which the patient is suffering from the disease Alzheimer's disease, HIV, breast cancer, diabetes, endometriosis, gastritis, heart disease, hypertension, kidney disease, multiple sclerosis, prostate cancer specific skin cancer. So in this video, we'll be doing till here in my next video, I'll be discussing about the next part of the SQL classes and just for now, let me end this video over here. Thank you Goodbye. I'll see you for the next video.