Transcript for:
Advanced SAS Programming Tutorial for Beginners - Naidu's Lecture

hello everyone this is naidu i'm from great online training and in this video i will explain advanced sas programming tutorial for beginners so in this i'll talk about macros so that is your sas macros sas sql and graphs we'll talk about in detail so let me just go through it one by one so in this particular video with me so few of our students in the uh right now in the line so can you just introduce yourself hello yeah um this is sandhya and i am from uk thank you hello this is mary from united states richmond virginia thank you hi this is sangeeta from usa south carolina hello everyone this is aditi rati from india good so thank you all and so let me just go through this presentation first of all in this particular uh presentation i'll try to cover uh agenda so for this particular uh video is i can say the sas macros i am going to cover sql and sas figures in detail first of all what is sas advance so the sas programming is divided into two parts one is basis programming and second one is advanced programming so basis in the sense uh it's up to some topics it is not that uh it's a difference from uh it is completely different from advance or something like that so the total syllabus is divided into two parts one is the base and second one is advanced so advanced week comes under advanced topics comes under like a sql graphs and as well as macros is comes under your advanced topics and whereas coming to your base topics like proc report proc frequency proc means what i can say uh proc print proc contents and all those stuff is comes under your basis programming as we already covered the basis programming in our training so we are move on to the advances as programming so today we'll try starting with learning a macros so here what is sas macro programming so what we do with this macro programming so generally in every programming tools like you just go for excel or else you can go for java or maybe a dotnet or any programming framework you just take it so in everywhere we have macros macros means the meaning of macro is if you want to do some work in a repetitive manner instead of doing the writing the same program again and again we try to macro ties it so that means you no need to do that job every time just take it and let's take a small example you want to sort a data set okay so there is a data set for you and you want to sort the data set so you can directly write proc sort data equal to your data set name for one data set you will write a certain processor and you're done imagine if you want to do the same action for hundred data sets so you need to depend on macros so what it will do is if you want to do some work again and again so acting the same on the data sets with the same thing because proc sort data equal to your data set name and by which variable wise you want to sort it and semicolon and run for one data set again second data set also third data set also like that around 100 data sets also we can do it in a quick manner by using macros that's the reason why macros we use so if you just looking into the macros what exactly the sas macro says sas macros are used to automate the repetitive tasks let's say as i said earlier is any task you want to repeatedly doing it we depend on this particular macro it can make your work faster by automating the task requires writing a same line of code every day suppose as i said earlier if you want to write a proc sort concept every day you want to write it short concept to doing a sorting of hundred datas at 200 datasets or something like that or else maybe you are trying to create a proc means the proc means you want to use it every day so instead of just writing your own program again and again try to write it as a small macro that macro will help you to do that work in in be off of you that means you can save your time and as well as you can work faster than normal when you are using macros that's a main agenda before you write a sas macro you need to understand something sas macro code is separated from the sas code so that means whatever we are writing its as macros is completely separated it's not a completely the same code the coding wise it's a different but the inside of the macros what we are running is again our basis programming only but the outside of your base as programming whatever the lines of code what we are writing it it's a completely new programming language i can say so it's a completely different from your basis programming and the syntax is also different it's not the same syntax like our regular syntaxes the syntaxes also will be different somewhere that we will see in upcoming class so the sas macro rules the macro rules even in our basis programming also we have some rules like a it must be start with a character or underscore embedded blank is not accepted name and it can be upper case or it can be lower case and all so we have some rules even sas macros also rules mainly in sas we have a two types of triggers we generally use one is amphis and sine and second one is percentage sign so amphis and sine is used when you are trying to call some macro variables percentage sign is used to call a macro right now so we just try to give it ampersand and percentage when you program it somewhere you are writing it amp center somewhere you're writing as a percentage you are giving a signal to a word scanner that is in the back end of your sas to execute this particular program into your macro compiler instead of a data set compiler generally in our sas backend we have two compilers one is macro compiler and second one is data set compiler when you write a data your data set name semicolon input cards and all the statement when you write it so that's your data set programming so that the data step compiler will execute and it will give the output to you but imagine when you are giving a data directly uh from if you are giving in your code like ampersand sign it somewhere percentage sign it somewhere this is triggering to your sas software to use and to submit this particular programs in macro processor comply compiler in the back end so it will be executed as a macros it's not as a basis programming macros macro variable can be referenced anywhere in the sas program other than in a data align statement generally what happen in your basis programming if you want to derive a declare any macro any variables you will write a data your data set name and after that input statement you will write it within that input statement only you will write it idp id age sex gender something like that you will write a variable names no in macros you can define anywhere in the sas environment it's like our global options titles footnotes no caps no number no date no center and all we have already seen on our basis programming like that we can write it anywhere we can declare it anywhere in the sas environment it is not that compulsory we need to write it before input and we need to write it after input nothing like that we can write it anywhere in the sas environment but the only limitation is you cannot write a data lines in your macros okay you cannot write any data lines so that means you cannot create a data set within the macro that's not possible we can't do that macro variable values text value up to 65 k that means 64 k so that means 65 534 characters or bytes you can increase it generally in our bases programming generally what happen up to 32 767 bytes are characters we can increase it but macros we can increase it up to 64k that's a maximum size of a macro variable what we will have it and the sas name so valid sas name the same rules like our base as programming and as well as macros macro programming the same rules will be the same it should be a valid sas name what are the rules for valid sas name it can be upper case it can be lower case it can be start with a character or underscore embedded blank is not accepted sorry all the rules will be the same and the macro variable names up to 32 characters we can give it that's the same as your base as programming there is no doubt in that only the variable values you can increase it up to 64k so that's a max you can give it so these are all the rules about this particular macro programming do you have any questions here anyone no sir thank you so now let me just go for macro facility the macro facility is a tool within a base software that contain a essential element enable us to use a macros what happens you know so in the in our basis programming tool itself there is a software it's already backend so that software is enabling us to use our macros right so that's the reason why we can able to write a macro programming at all imagine you have a license for only basic software then you can't write run your macros that's for clear so we have to have a license for macros also to run it then only you can able to do it the macro facility contain a macro processor processor that translate a macro code into a statement used by the sas system and it will be converted as a macro long ways so what happens whatever the statements you are passing it like a data your data step name and after that cards and all what you are writing it right everything you it will be just split it into a statements like a single single words that we call it as a small tokens it will be converted and it will be submitted to in the back end of your sas into your macro processor macro processor will take care of the job and it will run in in the back end macro language provides that means communicate with a macro processor so generally macro long ways means what exactly it is doing it it will try to communicate whatever the program you are writing it that program try to communicate with the macro processor and it will be get executed in that location you will get the required output according to your requirement whatever you produce it macro consists of macro language consists of a set of commands and options and syntaxes and compilers also that means and what happened you know so all the same like our basis programming whatever the command you have it some of the options you have it and as well as some syntaxes everything is same like our basis programming so which is already there it will be work in the back end so to execute your macros now we will move on to the defining a macro so as of now we just talk about a theoretical concept like what is macro and all those stuff and how we will utilize this macros in real time first time we are writing it right now how to uh define a macro here so generally what is the way of defining a macro here if you want to define a macro you start with the percentage macro and end with a percentage m and statement so let me just go for explaining the syntax how you will start a macro percentage macro and your macro name and then you will write the syntax what you want it and then amend and you will write it the macro name suppose my macro name is used for sorting of my mac sorting of my data sets every day so then i'll write it you see that the example this is your sas code and this is your macro code the sas code is proc print data equal to your data set name self.class and run that is my general sas code if i want to convert into a macro code you will write it percentage macro and you will write it the macro name my macro name is print that means it is just used for printing purpose in real time whenever you are try to use a macros you will make sure to save that particular macro name with the purpose your macro name is mainly the purpose is for printing then you will create as a print macro suppose your macro is used for creating a mean you will write a mean macro or else your macro is used for uh to calculate the frequency it's a frequency macro something like that if you name it even in future when you try to refer okay what this macro is talking about or maybe someone else is take care of your job in behalf of your absence they'll also get to know that what macro you are using what is the name and all by looking into the name itself they'll get to know that purpose of this macro and then they can able to continue further so make sure to give a user friendly name instead of xyz abc don't give the macro names in real time like that try to follow the naming conventions your purpose is going to be your macro name maybe don't give it my macro is used for sorting purpose don't write it a big lines of code you try writing it simplify it and give a proper name for that if it is the purpose is sorting just srt macro if it is a print purpose write a print macro if it is mean write a mean macro if it is a reporting purpose then report macro you can write it like that as a name so that no it will be easy percentage macro print i'm writing it proc print data equal to your data set name and run and percentage amend and print what we do is we try to execute this particular code in our software so for that let me just open my sash let me open the sas software good so now if i want to write the same program generally what we can do proc means it's a let me write it proc print data equal to your data set name says help dot class i'm writing it and run perfect right when i execute this particular program by using ctrl a and run it yes the program is got executed around 19 observations containing a data which is there with us and it's got executed perfect what we can do is we can use a macro what we can do is simply do the same percentage percentage macro your data set name you will write it so here you can write it the macro name is print or something you can write it the macro name and within that you try writing the same code like a proc print data equal to your data set name says help dot class you are writing it semicolon and run percentage m end you are writing it and your macro name is you're just ending it print semicolon that's it you're done this is just for creating a macro so once you run this don't expect any output once you execute it it will be get executed that's it it won't give any errors or anything the macro is just got created and with the result of this go to your explorer window mainly libraries and go to your work library there the sas macro the print macro you have created so you can see that this is called macro catalog macro catalog will be created and it will be stored so now you are storing it how we can utilize it this macro if you want to use this for your purposes you can write it percentage okay print p r i n t so you can give it in my keyboard some of the keys are not working i'm writing it print i'm writing it a semicolon that's enough only this piece of code you execute it from here you run it and that's enough you say execute it see automatically macro will run not only that maybe in this particular macro you use the a set of codes for printing maybe you use the set of codes for calculating a uh like a frequency you can write it proc freak data equal to your data set name i'm writing it says help dot class semicolon and run so that what happened so you execute this particular code it's enough it will be calculated when you execute print is the program name is enough it will calculate your print also it will calculate your frequency also see frequency calculated at the same time print also calculated you can see in this the first one is it's giving a print the second one it's giving a calculating a frequency like that the total number of codes what you want to write it you can write it inside of my macro just for calling purpose you just write this particular piece of code is enough to submit or to execute all the program is it clear anyone have any question on this what is the general programming how to convert into a macro programming so like uh i was just wondering like where should we use the ampersand because uh in the powerpoint we saw ampersand dataset names let's see if i declare my variable as a into this i'm thinking that i will not give this saselp.class right now in future i will give it whatever the dataset name i want to give it so right now what happened if i'm writing my dataset name here so it is a a static one instead of that i'm trying to make my program is the dynamic way so that no in future if i'm passing it that class at somewhere it will be populated everywhere so i'm writing it here uh as a a equal to if i'm writing it or else maybe you can write it dsn write it directly the sn okay instead of that you can write it any name you can write it as a dsn that's enough so here in amphison sign you write data ampersand dsn and here also in future is required for me the data set name i'm writing it ampersand dsn that's enough so when you want to use it how you will use it you know you try writing the same in the program you will write it d s n equal to what you want to declare maybe in in this particular case it may be a saselp.class you will write it as help dot class you will write it that's enough so that what happened this coding if you execute this particular line sasharp.class will be executed for today maybe imagine in future it's not saself.class you decided your mind you change your mind instead of sas help i want to use a cars so then i can directly use data set okay is it karak or maybe i have a data set called air go for air so now what happened in that data set i already have a data set i want to use it so i can select my program and run it so that a data set wise you can see that it is giving a proc print also for a data set wise it's a printing of my a data set and the frequency also calculated according to my data set wise you can see the frequency possible cut it understand where we use the ampersand sign ampersand is just to refer what we are giving it here when you are passing this value this value will be carry forward by using ampersand sign method percentage macro and dsn is a macro name uh so macro variable name you are passing it so that variable will be having the value of here instead of self dot air just class you can give it anything this is how we can do it clear there so macro print this function right in this n is a very macro variable ds in is your macro variable and print is your macro name you're telling us has to run this particular macro using this particular data set i'm talking about the for line number four line number four okay tell me what you are saying so it says macro print is a just one line of code right it is a function macro print it's not like that do one thing instead of print you can write it your name also marry don't write it as a print write it as a marry and here also you can write it as a marry it is not mandatory that definitely you have to use print always you can go for mari that's your macro print data is doing the function right then yes data is your basis programming activity as it is what you are doing it inside but what you are declaring it it's your macro name the macro name is marry now execute it and run it yeah it will work without any issues you will create a one more catalog see it's working and you can see instead of print marry macro is created now got it thank you so much sir yes shall i move to the next questions right so this is your general code and this is your macro code perfect clear now what we can do is we can just calling a macro as we have already seen here so how to call a macro so you're writing it proxot data set name and all so calling a macro in a different way right now what i have explained to you i have explained directly uh just giving the values into my sas itself i directly given uh dsn equal to i'm just writing it my data set name what i want it right instead of that you try to declare it let's say i'm trying to write it the macro name here for my sorting purpose okay i'm writing it proc sort data equal to your data set name i will write it ampersand dsn and out equal to the new data set name i'm writing it ampersand out equal to in it write it so i output data set i will write it ampersand no i will write it and by which variable wise you want to sort it by ampersand okay i'm writing it the variable name may be var one i will write it semicolon that's it and run what this will do i'll try to put this name as instead of marry this macro name is for sorting so this macro is for sorting i'll try to write it as a srt is my macro name maybe you can write it the macro name is srt again as i said earlier you can put it any name whatever you want it so you are creating a macros now you're running a macro now perfect srt macro created now and how we can make this work you how to use a statement called percentage let percentage let is the method is used to create a macro variables generally in our basis programming to create a macro variable we generally use it input statement and all if you want to create a macro variables in our sas macros you need to write a statement this is one way so we have around five years for that so this is one way of creating a macro variable by using percentage let by using a percentage letter you create it so what you wanted so dsn equal to so dsn equal to you give your value what you wanted to give it it's an ss help library with the name of class data set i'm writing it cl asses class i'm giving it and semicolon one macro variable you created now you are trying to create one more macro variable percentage let what is another vector variable name in my case new equal to yeah what is your new data set name you can give it any name i'll give it for my new data set name as a naidu after sorting my data semicolon and then i'm writing it percentage let i'm writing it here uh ampersand where one right ampersand var1 means i'm declaring my var1 variable value var1 equal to i'm writing it based on the sex variable wise i'm writing it that's enough so what you have to do is you need to declare this particular macro variables and execute it it won't run anything it is just executing it and that's it it will stop now your macros also your macro variables also ready your macro programming also ready you combine everything by just mentioning of percentage um tell me percentage what you need to write srt and semicolon yes sir and one more is here you're writing it dsn is one macro name right uh do one thing don't write anything dsn or anything you directly write it like this percentage srt and your macro values you are directly writing it percentage srt or macro and ampersand dsn ampersand new ampersand uh where one and all you are writing it now percentage uh srp right it's not str str is a function which is there in our sense so srt you will write it and semicolon and execute it see the data just got sorted understand you you want to see that is it really sorted or not see the data just sorted and it's created a new data set name called naido and having 19 observations in my new data set you just go there see an id data set is created and the data values get sorted based on your sex variable wise making sense yes no this is the purpose so okay suppose in future you think that instead of sex you decided to sort my variables so based on my uh is variable wise instead of sex what we can do select where one is equal to is huh so now instead of r1 you just write it yes that's enough man you just rerun only this piece of code is enough you rerun because we have done some updates so that this particular line of code need to be written and then write it percentage str and execute it then now you can see the new data set values of naido is get sorted based on the age variable wise if you want to check it can i just check it is it based on the age yes or no yes sir perfect it uh over it override this previous one or it is if we give uh it will give new data set name so it will create new name no it's automatically you are giving a new data set as an id right first time when you are creating i do second time also when you are creating an id world one will get overwritten with a new one made up if we want to keep it so we need to give we need to change neither to different one right yeah if you think that you want to keep it you need to give it as an id1 or something then you can able to have my old data set with you so that you will have a one more data set with the name of knight one there instead of having an idol so now you check it and execute it there will be one more data set with an id one will be with you making sense thank you sir yes yes sir yeah so if it is like descending sorting ah then if you want it to sort in a descending order you do one thing you write it so d e s e n d i n g is it okay descending of your age you just pass it so what happened new data set values descending by age can you see that happening yes sir how the descending is working the line of code what you have written in a r1 statement it will be get pasted as it is into your by statement here it will be pasted descending space age and not only that if you think that you want to have uh one more variable called sex also you try writing it sex here that's enough understand you don't need to create a new variable for that and all you can write it within a same line code of itself within the same code itself descending based on the sex variable wise you can see first descending within that mail first and then followed by female or something like that according to your data okay so fine thank you sir yeah it will clear shall i move to the powerpoint presentation any questions good no questions clear sir thank you so now we'll go for macro variables so macro variables and basis variable naming conventions are same like a basis variable also what we do percentage uh generally in our macro percentage let me call it but in our basis programming input and all we try to create it but it can be up to 32 character long the variable name it can be upper case it can be lowercase embedded blank is not accepted special characters is not accepted except underscore sign so these are all the rules right all the rules will be the same for your macro variables also i just used a macro variable name by using percentage let right by using percentage let i have used a var one percentage dsn percentage let new and all those are all my different macro variable names i'm just declaring it the macro variable names lengths and everything will be the same the values you can store up to 32 uh basis programming you can be able to store it up to uh 32 761 bytes or characters you can store it but in your uh macro variable if you are writing percentage letter you can store up to 64 kb you can store it by default macro variable names not data sorry macro variables are not a data set variables sorry okay so macro variable names are not like a data set variable names so these are all different it belongs to your sas macros actually so those are all not same like a we generally use percentage letter and all so percentage let's sign why we are using it if you want to make that as a macro variable so you need to use percentile net otherwise you can write a equal to a equal to 20 b equal to 30 or something like that we can write it already in our data step method we already know that how to declare the variables no when you are writing a percentage letter that means we are trying to create a new macro variable okay so once the macro variable is defined it can take any value during the execution of sas program okay so suppose what happened you are declaring a macro a equal to 28 somewhere and within a program the calculation and again after a few lines of execution you decided you want to assign a new value for that a equal to 30 or a equal to 40 or something like that yes you can go for while executing the program itself we can add any value if you want it okay suppose imagine let's see let me try to explain with says you already declared the variable name uh naidu 1 here and you declared it for srt now i'm trying to copy the same thing but this time i'm writing it somewhere instead of knight one i'm trying to create a new data set name called ff so that what happened is the same variable name it's the same but we are assigning a different value so when you are executing it here it's a different value when you are executing it here you will have a different value you will get it by default understand so that means we can assign any values to your variables in upcoming when you are executing the program yes you can do in that way possible i'm sorry sir it's not gonna be the same thing you should just change it then so the the variables will not be same thing observations and everything er not same thing because we just changed the name of the you see this what happens you know opening sass see i'm writing it if i'm interested to create the same program as it is i'm writing it percentage str or as percentage srt your macro name i'm writing it and semicolon so what happened now if i execute this piece of code it's gonna be the same thing it's the same can't be able to get a new data set name as a ff instead of one yes or no that is it name new one will be yes right that's what i've changed and here i changed it as in 91 it's working but now i'm changing to ff it's working on the go you can change it whatever the names you wanted you can assign to a variable understand it's not a fixed one earlier what happened in your basis programming if i'm writing a data or data set name input statement x is a variable if i'm giving a 2 throughout the data set the value will be 2 only right everywhere right but where are coming to here we can freely assign a values whenever you want it so now you can see a new data set is ff is just got created possible we can on the go itself we can change the values if you want it yeah you can go it go for this and the macro variables as i said the ruling rule so it can be up to 32 characters for base sas programming but sas macro variables up to 64 characters you can store it that's the default right good sir i have a question yes please yeah like uh as we saw now like for the prop print and proc sort we could do do the macros like is it limited to any particular uh proc steps can we do even charts and other things anything the same anything in the science you can do every everything so only limitation is let me show you that also so thank you you can write it proc g chart also suppose you are interested uh within this particular code itself you think that uh you're writing it proc proc g chart data equal to your data set name ampersand dsn i'll write it okay v bar vertical bar okay so based on the sex variable wise we want to pass it right the values don't give it ascending a's or anything you want to give it as x v bar within a vertical bar ampersand so your var one that's enough madam i think you write it one more statement called run what it will do now it will sort the data okay it will sort the data at the same data set wise the information okay here instead of ampersand dsn you have to give your sorted data information the sorted data set in some information as a ampersand knew you give it here and maybe you need a graph based on the two categories why success ampersand var1 you can write it here i'm just elaborating my program so that no you will be able to understand now i'm using buy also so that no i'm putting a link i'm sorting it i'm just using the starting concept for my graph concept also let's see will it work or not yeah if it is a sex equal to female separate graph is generated you just see in your graph sex equal to female separate graph sexy called male separate graph is generated based on the by statement because i have used a by statement in my proc step so it is just splitted into two yeah thank you sir so we can use anything the only limitation is let's see let me try writing some limitations i'm trying writing it my program name srt i'm writing it data my data set name kk i'm writing it here input statement i'm just specifying it x is one variable and y is one variable i'm writing it semicolon card statement within a card statement i'm just specifying it x value 10 sorry so x value 10 x value 10 here and y value 20 and here it's a 30 and here it's a 40 i'm writing it i'm done with my program and giving a semicolon and run statement don't expect to create a new data set with the name of kk now if i execute data okay if i'm running this particular macro execute it now see log will show this message data lines that means the macro srt generated sas chords are for the data step which could not cause incorrect results which could cause incorrect results and all that means the only limitation is you can't write a cards or data line statement within a macro as i said earlier within the macro we can't write data line statement that is the only limitation madam rest all will work with your basis programming if you yeah modify concept update anything will work yes [Music] so like if i'm understanding right you can't create the i mean create a data set with the data step in the macros yes data step with cards containing data step not the general one see you can do this one without this without input and all you can see write data set okay if i'm creating it set okay here you write it says help dot class if i want to make it yes madam you can do that there is no doubt in that the only limitation is you can't can't use you can't uh create a data set by using cards or data line statement by using macros that's the only limitation thank you so thank you can we use infile and do sir huh anything you try anything now you can use in file from the day one onwards you can try you can use your library statement if you want it you can use your info you can use your formats and information you can use your proc sort concept you can use your formats also you can use anything in macros except for the data lines accept data lines yes you're right listen clear any questions do you have any questions no sir thank you sir thank you now we will just move on so you are clear with this particular slide so let me just go with this one so types of macro variables what we have in our sas so the macro variables are you can say is divided broadly divided into two categories user defined macro variable and as well as system defined macro variable so user defined macro variables means you are the user and you can define the macro variables what you want it apart from that you have some system defined macro variables also so let me just say what are all the user defined macro variable and as well as what are the system defined macro variable when i just open it user defined macro variable again classified into two categories local macro variables and as well as global macro variables that means you are the user you can declare some of the variables as a local and you can declare some of the variables as a global also if required yes we can do that now we will move on to the next one here within that local macro variable if you want to declare any macro variable you will try to write it as a instead of percentage let we try to write it as a percentage local so that no it is going to be considered as a it's a local macro variable if you want to make that particular variable see in local macro variables also global macro variables also we need to create it with percentage let only so to consider that as a local macro variable to consider that as a global macro variable you can tell it by just mentioning that percentage local abc that means this variable will be work within that particular data step or within that particular macro only that means it will work inside of that macro is going to be local or else if i'm saying percentage global so that you can work on this particular macro anywhere in the sas environment so it's a global macro variables apart from that you can see some system defined macro variables the system defined macro variables are nothing but your sys date sysdate nine dot system version cis user id and all you will get it so now what we do is we try explaining all these type of macro variables how we can do one by one in our next slide so you clear any questions on this please speak out okay so the abc is the variable yes abc is your macro variable name percentage local abc means the new macro variable name you are creating with abc okay so percentage local will be the statement and abc will be the variable variable name yes you are right yes so now i'm just going for the next slide so as i said there are two types of macro variable user defined macro variable local and global right local variables defined inside of the macro definition are considered to be a local variable okay that means those are all local variables that means we try to create it within the program itself it will work only with that particular statement let's say take a input statement generally when you write it input x that means the xvl variable will be available only for that particular data set only that means it's a local right again if you go for titles and footnotes those are all global macro variables you declare it one way one particular place as a global options you can use it anywhere that's a global macro variable that's what it happened global variables are defined outside of your macro definition or inside of the macro definition specified as a global and considered to be a global macro variable once you decided by using percentage let within that particular macro or maybe outside of the macro you declare it that will still consider as a global macro variable for future let me show you some of the examples what exactly the global macro variable and what is the local macro variable first of all before explaining that there are ways to create a macro variable as i said earlier percentage let is one method as i said earlier to create a macro variable and apart from that we can create a a macro variable by using percentage local also percentage global calls import sql into there are the five different ways to create a macro variable in sas if somebody asks a question how many ways you can create a macro variables means it's a five ways by using a percentage light percentage local percentage global calls import and encycle into method let me just go through it one by one what we can do with it and one more thing is with loops also we can able to create it the macro variables yeah that's possible total six i can say so one is percentage second one is percentage local percentage global calls input sql into and with loops also let me just go for one by one defining a macro variable as we already know that how to define a macro variable by using percentage let's sign you will write it percentage let and after that your macro variable name you will write it the macro variable name you can write it anything percentage letter a equal to 20 you can write it like a percentage letter d name equal to sample that means i'm just creating dna as my macro variable sample is my value i can write it anything like that okay so the quotation marks are not required and used a part of a value suppose generally what happens you know if i'm writing dname equal to quotation sample if i write it generally we write it for a variable suppose uh sexy is a variable if you write it sexy you will write it in a quotation you will write it as a mail that means you are considering that as a character variable when you mentioning it equation but macro variable no need of having any quotations okay you write it percentage letter a equal to abc if you write it don't write it abc in a quotation mark quotation marks if you write it that's also comes under a part of a value so that means when you try to print that percentage let a wherever you call it the quotation marks also will come there don't use the quotation mark while creating a percentage let's sign without writing a quotation marks itself we can able to manage referring macro variable generally as we already know that percentage let is the sign to create a macro variable if you want to call it you will use it as a ampersand sign let's see percentile let d name equal to sample i am writing it percentage macro contents use my macro name proc contains data equal to ampersand dname and semicolon and run what is that mean proc data proc contains data equal to your data set name sample and semicolon and run that's a statement what we are going to do with it percentage let is the sign is used for creating a macro variable and you can use it whenever it's required let me just do that with my sas as we already know that so we are already doing it here you see that percentage let we are just using it uh dsn equal to self dot class what we can do is simply data your data set name you can write it k set you can copy your values so you write it as a set and ampersand you write it dsn is enough semicolon and run what happen if i run this particular program what do you think is going to happen if i execute it new data set will be created with the name of kk and taking the information from the self.class you just go there kk is a new data set is copied from your sasharp.class is it working you created a macro variable by using percentage let dsn equal to sayself.class and you are using it for your purpose whatever you think that maybe you can use this particular data set for your print purpose also you can write it proc print data equal to you can write your data name ampersand dsn and run that's working there is no doubt in that if you want you can able to work this data set as a printing purpose also you can do that that's possible execute it yeah it's working for printing purpose so close this now so that is just for creating a macro variable and calling a macro variable anyone have any question on calling a macro variable you created by using a percentage that if you want to call it ampersand sign you need to add it clear yes no sir i have a question like right now we just gave the data set name and proctored uh the print procedure and we are just using ampersand bsn and percentage led for dsn is defined but like you know we are not using percentage macro anywhere right ah okay so if it's just for directly creating a macro you think that you want to make it as a marketizer thing okay instead of just manually running it try writing it percentage macro you write your macro name sd i'll write it the macro name okay semicolon and you try writing this step it down saying it as a semicolon if you want to call this macro percentage sc semicolon is enough yes this will working okay even without that it is working yes with also without also okay right the new data set is just created you just go there k data set is created you do one thing maybe kk is already created if you are not trusting it try to create a kkkjade as a new variable new data set let's see will it work or not execute it run it see kkz is a new data set you just got created yes agree we can write it right also with macro without writing a macro because it's a global macro variable actually percentage let if you say it it will work everywhere it is not that mandatory so we have to write it within the macro you can write it outside macro also we can write it inside macro also percentage that sign will work yes madam please continue thank you um uh amphis and dsn is the data set name right so so how are we calling the variable here where percentage let is my macro variable i'm using it and the value of my macro variable is dsn equal to sas help dot class i am passing it as my value for my data set okay right forget about this particular 19th and 29th line you just think about that percentage let dsn equal to saselp.class so that if i call the dsn that means it's a sashf.class okay so dsn is the variable name over here yeah so instead of that you can write it adt also if you want it okay okay you try writing it a d t here also will it work yes it will what is the problem right the new data set is created with the name of k z so that means k is it yeah it's working you can write it any name it's not mandatory that you need to use dsn you can give it any name that's up to you it should be a valid sas macro variable name that's it okay sir one more follow-up question yeah like sir we have given uh data kkz right instead of that also we can give ampersand uh dsn or something but in that case yes you can give and dsm right percentage let okay so give it as a new dn new dsn okay nds n or something like that equal to you can give it a new data set name as a kk you try to give it a semicolon here what you do is ampersand yen yes n you write it is it enough i think it's enough yeah but we should give sg and in bracket also we should give is it no that that forget about for that so that is a positional parameters macro and keyword parameters macro when i'm talking about it that we will talk about in detail for a while you can create it in this way yes okay yesterday's class we had this on the train because i want to explain in detail over there so that's the reason why actually we do have a plan for that so there will be one more slide talking about that what is keyword parameters macro what is positional parameters macro there i'll talk in detail for right now i'm just keeping that for you to avoiding the confusions okay so thank you yeah it will work as a new data set you can write it so there is no error good ok come to our presentation so referencing a macro variable we have done so now how we can able to declare a global macro variable how we can declare a local macro variable let's see what happens if i'm writing it percentage macro in is my macro name i have given in a brackets as a where percentage local ss percentage let ss equal to ampersand percent is put then inside and we are writing it what does this mean what exactly happened i mean the percentage local the local macro variable that also we have to give percentage left and we have to define is that right yes let's see generally what happens you know uh we'll try copy the same program as it is bring to sas i'm just deleting it all those things because to avoiding confusions i've just written percentages let percentage macro in is my macro name var is my statement i have given percentage local my macro variable name as ss let ss equal to ampersand where percentages put inside of ampersand b percentage put i will not write it anything for a while let's see what happens okay percentage in percentage let i am giving a value ss as a fire i am giving it in value i am giving as a 3 what value you are expecting now if i execute it uh why are we giving in of three sir because washed in where place wherever we are giving it that value you are giving it as a three okay okay so that means actually what happens you know if i'm writing it i'm writing a variable value bar equal to i'll write it okay so where equal to i'll give it as a three i'm writing it where equal to so 3 i'm writing it so what happens now when i'm passing the value 3 here um so you declared it as ss as amps and where and in equal to three you're writing it ampersand three so if i run the program what happens now let's see what is the value [Music] will be three right so now where it will be displaying if i'm writing it percentage put ampersand ss what will be the value why it's displaying five there is no ampersand sms is there but we should assign these percentages okay you run it and you're declaring it now tell me what is the value still why you are getting not why you are not getting a three instead of five we are getting it i think because of that percent right if i write in the same statement within my son we are not uh sorry sorry uh like we have in line number three we have ampersand where but where have we written what is where in of where equal to three that is the definition for where huh we have written right n equal to var means three when i'm passing it value i'm expecting a three so try writing it this person just put inside okay [Music] now we are getting three what is the reason do one thing you see you try put that particular percentage put outside macro yes where equal that's okay now still five why because when you are declaring it sorry wait can you see five in the log window you can see five right you see ampersand five actually even i couldn't oh okay yeah thank you just avoiding a confusion okay if i'm writing it percentage let please put them in percentage let if i'm writing it the value a equal to 20 a equal to 20 if i'm writing it semicolon if i'm writing it percentage put percentage put is the sign to print the values in your log ampersand yey tell me what value you are expecting now should be 20 yes 20 in your log also 20 percentage ampersand a value is 20 20 as it is displaying it there is no doubt in that okay now i'm declaring it what i'm declaring it data our macro i'm writing it within that macro so i need to write it with percentage percentage macro i'm writing it within that macro i can write it the macro name anything kk is a macro name i'm writing it i'm writing it value equal to x equal to i'll create it okay then semicolon i'm writing it right percent is late i'm writing it percent is late or else percentage local okay percentage local yeah i'm writing it okay so according to this you just see what happened percentage local your macro variable name you write it percentage local a equal to i'm writing it and i'm just giving my value as a you can just write your macro variable name what you think that so you don't write it anything you just given a here okay that macro variable you created as a now percentage let you write it okay uh here abc equal to okay i'm writing it ampersand percentage m and right so you just just try to stick with this so percentage let ampersand let ss equal to you are trying to write it ampersand var so now you are trying to write it percentage ampersand x value you need to write it instead of this you try to write it x there ampersand x what happened so you are declaring x variable in future whatever the value you are trying to write it as a abc of that particular value and m and i am writing a k here also you write it abc percentage let abc value you are giving it and here also abc you are giving it personal let abc equal to 20 percent at abc so that there is no problem the 20 will be printed as it is in your log right that's no doubt now come here percentage macro you are writing it k x equal to you are giving value percentile let person say local you are writing it abc i'm writing it here within the abc person they let abc equal to x and run it so now if i run this particular macro i'm giving a value percentage kk i'm writing it i'm passing my value k equal to if i'm writing 30 right instead of yeah it should be x x equal to 30 if i'm writing it semicolon what happens what value it holds now 30 should come because 30 so now we are expecting now you write it percentage put ampersand if i write it abc what value you are expecting do any is it a 20 or 30 we declared the a b c outside also we declared the abs inside also now when i'm writing it percentage x equal to 30 percent they put abc ah now tell me first of all you run this piece of code or else do one thing try running it everything here i try to maintain the space should not use the space it should be combined like this you write it what you are expecting why you are expecting 20 i'm expecting 30 right understand what i'm saying because it is global ah so what you declared percentage at a equal to 20 it will work outside of your macro but when you decided to use the abc as a local it will work if you write the same percentage inside of your macro what do you think what value you will have it now then it should be 13. now you will have a 30 yes you're right but uh i have one question sir yes let me just explain this you're getting 30 that means when you decided to use certain variables as a local it will work within that particular macro only we'll work it as a local macro variables right maybe if you think that you want to make that as a global you try to write it as a global so that now what 30 you are expecting now if i run the program is it 20 or 30 if i write it percentage global abc i'm writing a 30 if i run the percentage puts put sign outside of my macro what do you think is going to happen 30 okay 20 is it still 20 because you put global it's a global that means outside also inside also same right so the old value of 20 will be overwritten and now you can see the value is 30. try to understand what is local and what is global once the particular variables is declared within the macro itself as a local that means within that particular macro only the value will work as a local macro variable when you try to print the values within the macro yes it will give the value but if you try to print it outside the macro it won't okay again to avoid a confusion what we will do is we'll try removing it kk data set instead of that i'll try to create it as a percentage local percentage local a b c x i am writing it a new variable name and percentile let abc x i'm trying to give it if i'm writing it percentage put a b c x do you have that variable such a variable as of now now tell me what is going to happen abc x is freshly created which is created within the macro only but i try to print it percentage ampersand a b c x execute it done go to the log why you are getting this error message amperance amperage symbolic reference abc x is not resolved why well not assigned it's not assigned yet yes you're right it's not assigned it but when you will not get that message if i put this particular person just put inside of my macro will it work will it work here if i write it yes no it should right yeah now go and check it below the value is 30 you are getting it perfect got it that means when you declare certain macro variables as a local it will work within that particular macros only not outside got it insert outside means where outside means outside see this is within the macro means starting from macro onwards ending with macro end this is inside if you write something it will be inside within this particular macro only that global local macro variables will work suppose the same macro variable you try make it as a global then what happen now tell me then it will work outside also as you said yeah it will work outside also if i'm writing percentage put sign earlier we used to get some warning right apparent symbol reference not not present or something like that not resolved and all you will not see that message anymore execute it and run it still you will have a 30 outside also even though you have written outside of my percentage put sign got it yes yeah questions still you can ask it's a very important concept in your real time yes please so sir like if we are using global we have to use the percentage put i mean we can use the percentage put outside we can also use it instead yeah and for local it is like only inside if we give the i mean we'll be getting the output yeah okay percentage sign is used to display the information in your log what variable value it holds it is not that mandatory you need to use it this one maybe in future when you are trying to create a new data set name as a you can directly write it as a data your data set name x and ampersand okay don't write x maybe you'll get confused so new i'm writing it my keyboard certain keys are not working ampersand i'm writing data new ampersand if i'm writing it directly uh saying it as a a b c x if i directly saying it set sas set says help dot class semicolon and run so i have to remove the space here now what happen if i run this data set what is my new data set name tell me new 30 right that's correct if i run this particular program so that the macro variable i have created in the within the macro but still i can use it wherever i want it it is not mandatory that definitely i have to use a percentage put you can create a new data set now you can see the new data set it just got created with the name of new 30 making sense [Music] you can create a macro variable within that macro you can derive it you can declare you can use it wherever it's required you can make a local variable you can make it global variable so you declare all these some global macro variables by using percentage led sign but you are not thinking you are thinking that that value should not impact on within that macro you can declare it as a local so that the values within work within the macro only when you say local if you are writing percentage let abc x equal to 300 also outside in your program within your macro still it will work whatever the value you are assigning it it won't get changed understand keep percent put within the block no no it's not that suppose if i'm writing it suppose i declared my macro variable length is local i'm writing it here okay just go for local i'm writing it a b c x let equal to percentage all are my local mocker variables but here when i'm writing it percent is let percent is let if i'm writing it abc x as a new variable if i'm assigning a value as a 500 tell me what happens now will it have any impact on your new data set what is your new data set name then it is it 500 or new 30 which one is correct 500 because local i mean percentage local is i mean as of now the line number two is global right so line number three is global so that outside macro you will run it you will have a new 500 as a data set name can you see that not no 30 because when you say it's a local it will work within this particular macro only is possible it will not work outside that's the difference between local and global you got a difference yes no yes yes if we make it local let that percent let abcx equal to 500 then what will happen can we do that okay so tell me what you are asking percentage let abc yes this one this one if you move to inside the macro like if we make it a local now it is global if we make it uh local what will happen sir uh local in the sense where you are expecting this value so this 500 where you are expecting me inside the market okay that means you are expecting me to bring it inside my macro that's what you're saying yes yes please sir huh then it will be it will be 500 in that case so you no need to use anything it will it will override 30 or it's gonna be uh new five hundred actually like in that case yeah yeah no i'm thinking um i'm sorry but like you can give x is equal to 500 so if you want the output to this prime thinking is the same why we need to write it if you think that you want it as a 500 then make it x equal to 500. then instead of local you make it as global or if that's up to you that's you want it so instead of 30 you want to see it as a 500 yes you can see you write it percentage put ampersand x what is the value 500 you are getting it perfect whatever the value you are passing it within the macro it's different outside the macro it's different the difference between local and global local you will start with local and you write a macro variable which will work within that particular macro only if you write a percentage global if you write a macro so that it will work within the macro also outside the macro also it will work that's what the definition is if i remember correct right so you see that when i'm explaining about the definition local macro variables defined inside of the definition are considered a local variables defined outside of the macro definition are inside of the macro definition should be as a global as considered to be a global variable yes am i justifying to these particular lines yes no shall i move to the next sir one more question so local means uh it will show this in log uh log side if we if it's global it will go it will make in the sas some i still didn't get it okay local means it will work this variable you want to write it suppose you created this particular c you just created so the variable x equal to 500 okay now you want to create one more data set inside saying it as a data okay so you're writing your data set name you can write it um c ampersand x if you write it or else ampersand a b c x if you write it set says help dot class if i write it semicolon and run what do you think is going to be your new data set tell me c 500 huh then that's it that's correct that means when i'm passing it a new data set will be created c500 perfect it's created but if you do that the same thing you bring it outside of my macro you try running it can you get it the 500 will it work no then because it's not global or would even sign it that's right you're right that's correct if you run it it won't work so we have given a b c x right so abc x we are trying to derive it um i think percentage kk what you have done you have given it as a actually put should come out saying for that's okay put we where we are writing also it not make sense whether you put it or not try deleting this actually i have not deleted it earlier just delete it the c is already open let me delete it if i'm writing x 500 let's see i have mentioned as a local abc x and all and i should get an error i think this macro variable we have declared it earlier global also i hope if i remember correct [Music] it inside i think yeah you try writing it as a a b c x c you try writing it a b c x c you write it a b c x c you see what happened yes it's not we're getting it 500 yeah it's not working you will get that error message because that variable is not declared yet it's only declared in local c a b c x so what we have done c a b c x a b c x e yeah it's a new variable earlier what happened you know i declared it as a global i declared it as a local that's the reason why it's working in both the sites but now i take taken a fresh new variable and i declared only in local but i try to copy it in a global so i try to try to run it in in this particular way it's not working you clear with the concept of what i have explained local global anyone have any questions at anything you just ask so like know for the error actually we've got the error message right like it says that can you please go to the log window sir yes so um what does it mean sir apparent symbolic reference abc xc so but it shows near the ampersand didn't find a macro variable defined in the form of a b c x c that's it so actually if the macro variable is already defined sas can call that but when you sas doesn't know that what is it ampersand and all then sas will consider it is a syntax error because we already know that we should not use any uh special character in our data set name am i right or wrong yeah that's throwing an error okay and one more thing if it was a global macro variable then it would have uh it would have been executed executed right you make it this as a global do you think is there any issue with this you won't no no so global you have written a new variable you have written and let's see what error we are getting it see what i have let me see 500 sir sorry already see 500 data set is there in the work library that's okay that's not a problem so what we are thinking is huh i think c 500 is there let me delete that i tried do it to make it 400 here so why to change that one let's see 400 but why you are getting a log first log error we are getting it first we try to fix that i think there is no error but still it's giving 500 because x you are giving it x is abc x and global you make it c a b c x [Music] it's not giving an error that means it should work ah c400 you got it now yeah you got it working right so this is about your local and global macro variables what we have it so we just done up to here okay now we are calling with ampersand cyan with macro variables how to call it and all percentage put statement we already seen percentage put statement writes a text in your current value of your macro variables in the sas log yes that's doing it right so when you are just looking into a program suppose you are writing it percentage let you declare a delete in everything from here you're right writing it percentage percentage let a equal to if i'm writing it 300 semicolon so i created okay just imagine i created but due to some reason i removed it that means the macro still is in back end i want to know that somewhere in my program it's called as a data my data set name uh xo with the ampersand a they have called it what is ampersand a value you want to know it then very simple you write it percentage percentage put ampersand a with semicolon is enough it will give the message what value you hold it in this particular variable go to your log log will tells that you have a value of 300 got it yes yeah percentage put is the sign is used to know that what value the variable holds it not only that you have some system defined macro variables also you can see what are all the system defined macro variables right now you created this macro variable that's the reason why what you are doing it you are just just hold on a second my sass is opened in another window so it is just created right so what i'm just thinking is i want to know that what are all my system defined macro variables is there in my sas then i'll write it percentage put underscore all underscore if i write it semicolon what it will do it will give all my macro variables list in my log you can see these are all my system defined macro variable which are all starting with automatic that means this date sys9 dots is dates it's versions its user id is his name and all you will get it the system defined macro variable and now you can see that this is my global macro variables which i have created as of now these are all my global macro variables the list is can you see that the list are yeah global macro variables suppose if you created any local macro variables that local macro variables list also you will get it here understand so you create any local macro variables global macro variables all the list you can able to get it here or else instead of that you want to see only the system defined macro variable you are not interested to see the user defined macro variable then you write it underscore automatic underscore you write it that's enough so what it will do is it will give only the automatic macro variables what you have it in your sas you will get that list of only automatic macro variables you see i have written percentage put automatic underscore automatic underscore automatic underscore so that all system different macro variables only you will get the list understand or else you are interested to see as i am a user what are all the user defined macro variables in this list then go for underscore user underscore so that if you want to get it only the user defined macro variables if you want it yes you can get it see i'm as i'm a user i created only these macro variables as of now am i right or wrong the list which i have created yes i'll get it only the user defined macro variable or else you want to know that only global macro variables i have created maybe the user defined you have it global and local also but i need only global macro variables what you can do write it global underscore what happens now if i run the program what is going to happen you will get it only global macro variables list these are all my global macro variables list so one question like there should be global way macro variables which are system defined also right yes or no all my system defined macro variables are global macro variables only okay so okay you can use it anywhere let's see i'm writing it uh proc means procedure print means you're printing it data equal to your data set name i'll go for sas help dot class i'm going for it semicolon and run i'm writing a title what i'm writing the title you know title is the statement i'm writing it within a title statement you need to make sure to give quotations with double quotations you cannot write a single quotations here because macros need a double quotations as of now we don't see any problem with single quotes and double quotations but here you have to make sure to get double quotations maybe i'm writing a title this is my student info as of ampersand cis date if i'm writing it what happened now what does that mean it will sign as of today my system date whatever it is so this is the students on info as of january of 2020 you will get it 3rd june not january 3rd june of 2020 you will get it if i run the same program tomorrow what happen the title optimism updated with your values yes or no yeah right maybe suppose imagine i want to write one more footnote also this report generated you write it as a footnote footnote quotations this output generated by cis that means ampersand cis user id on ampersand says date nine dot this date nine or maybe this date nine if i'm writing it let's see semicolon what happens execute it then it will tell that in my computer so this output is generated by pc according to my computer my system name is pc i just named it as a otherwise if my name is if i log in with my name as an ido so that it will be displayed with naido maybe sangeetha when you are logging into your pc your computer name is sangeetha so this report is generated by sangeetha the same program when you running it automatically output with your header or your footnote maybe if i am running the same program i will get it my my footnote right understand it's a dynamic one it's not always static that you are giving your name the date maybe when you are running maybe the date of your computer maybe by the time maybe uh 2000 that 2022 or something like that automatically the output will be generated with the latest data with the latest titles and all yeah yes no how these particular things are working but um sir like one question initially we defined uh percentage lit and we are giving a is equal to 20 right and then again when we are creating another um macro while defining there we are giving percentage local and then we are changing its value to 30. what happens is it is overwriting the value 20. is there a way to get both the values like 20 and 30 20 or 30 you can write it one is you can declare it as a local another one is declare it as a global then you can have it too yeah like my question is sir like suppose we have one data set in two libraries we are using likes as help dot and sas user dot to get that data set right so like that we can't get this uh value is it once you declare it it is always negative a equal to 20 at somewhere and you are declaring it at somewhere again personal at a equal to 30 20 will be get overwritten with 30. there is no chance for you yeah okay like our title statement here you written a title at somewhere this is my student data when you are declaring it somewhere again another title as a this is my employees data so do we have still the student data with you as a title still with you it will be get over it right automatically yeah yeah that's what see whenever you want it you can use it in this way let's see you can write it percentage let i am giving it a equal to i'm writing as 20 is it okay i'm writing it data my data name i'm writing it x ampersand a okay and semicolon set says help dot class i can write it and run statement is it okay this is one statement now immediately you want it again percentage is let you need it same a value only percentage let a equal to i am writing 30 now can we do the same as it is can we have two data sets or one data set what do you think now tell me what happens if i run it at the both lines of code what is going to happen [Music] okay so we'll have 830 i mean x30 actually x30 is one data set and x20 is another data set right what is the problem because but huh you see this percentage a equal to 20 you have run let's see you have executed the data set is created it's there with you with the name of x20 clear no matter if i change it here will it still affect the old one i don't think so right okay let me equal to 30 if i write it so that new data set will be get created you see x equal to 30 here but if i'm again writing data percentage let a equal to 20 or 30 or you think that if i'm writing it immediately percentage let again you're writing it foreign a equal to 20 and immediately written person at a equal to 30 tell me what happens then it will be only 30 for both only for your two data sets also thank you right so underscore user underscore automatic underscore and all the macro variables how to displaying your macro variables to be done clear thank you sir thank you so much good so we have done with this slide so let me just move on to the next one creating a macro variables with calls input yes as of now we have done creating a macro variables by using percentage let percentage local percentage global and all we have seen so this is another way of creating a macro variable in sas by using percentage calls input method what is this calls input method how you will write the syntax for this calls input you will write it within a bracket you will write a macro variable name and what value you want to assign for it let's say an example so data underscore null underscore why we need to write a data underscore null underscore c the thing is percentage letter is the method is used for creating a macro variable by using percentage let if i directly write it i can freely assign a value 20 30 40 i can assign it okay but that is not the case i'm creating a title saying it as a number of persons in this particular data set how 20 members or something like that as a title i want to write it okay let me write it in sas and so that no i will explain there when we will use it go to sas here when i'm writing it uh let me print it directly the title i'm writing it saying it as a title i'm writing it a title you write it in this data set how number of females equal to you're writing it maybe it's 10 now okay and number of males equal to i'm writing it as a 9 or maybe it's a reverse so i'm writing it as it that i know that in this particular data set of my sas help dot class we have nine members of females and ten members of males in my data so i'm just writing it and imagine semicolon i'm writing it now i'm writing a program saying it as a proc print data equal to your data set name so that's your says help dot class you are using it semicolon run now once you execute it run it you will get it the title as a this data set how number of females are 9 members and number of males are 10 members perfect there is no doubt in that i'll use the same title i'm just doing it for another data set i'll try copy the same data set i'm pasting it one more time i'm passing a new data set name as some ss or something instead of my cell i'm passing it ss here let's see in this i have a 19 plus 19 how many records 38 records in my data set right now the data set which is there with me as ss you open it you have around 38 observations now i am passing this data set ss here when i try to execute it do you think the title is reflecting as same or is it different title tell me now i have 38 records but the title is still saying it has a 9 records or 10 records only possible how we can update the title according to your data it should be dynamic way huh understand once the in future if the data get increased automatically number should be changed at the top title correct so that means the title number should be derived from the data set as a macro variable then only it is possible but percentage lat will not do that percentage of let's sign is definitely not possible so that's the reason why we need to see an option or we need to see a such a variable to be derived within a data set level and use that macro variable for your purpose wherever is required understand then this calls input come into picture what calls input will do calls input will derive the variable within the data and then you can use that variable whenever in future let's say if i'm trying to derive a variable here however i'm doing it deriving it set data your data set name ss set says class i'm writing it now i will write it a statement saying it as in the next one proc freak proc freak data equal to your data set name i'm writing it ss semicolon so table statement i'm writing it so what i will do i need to identify the unique records in this so based on my sex variable wise i'm trying to find out a unique records in this data set semicolon and within the same statement you write it out equal to the new data set name you're writing it so you can write it anything i'll go for ovo as a new data set name and default it's giving a print in my output i don't want it that print to be displayed i'm writing it no print semicolon run that's it now once you execute it the piece of code is created and the data set will be get stored with the name of whoa in that you have a unique counts of females wise unit counts and males wise unique count you have it in your data can i just see that female 2 is 18 members in your data males wise 20 members in your data perfect yes or no yes out of 38 records the 38 cards are divided into females are 18 and males are 20. okay now i want to make this as a a macro variable yes i got accounts now i want to make it as a macro can we do that then you need to write a syntax saying it as a data your data set name underscore null underscore because i'm not interested to create a data set name here understand what i'm saying i don't want it as a new data set but what i wanted i need a values from my data set of wovo okay i'm creating a new variable saying it as a i'm writing it the name saying it as a what i wanted the variable name so in this i don't want it for all for i need to filter the data only female data i want it right where equal to sex i'm writing it it should be in a bracket we need to make sure where sex equal to if it is in a f we are trying to create a only female data we are talking taking it as a new data set and then we are trying to write it the variable name according to this you see that calls import what is the syntax according to this data calls input and then followed by your variable name then your value so you need to write it the variable name the variable name you can write it anything i'll go for one i'm writing as my macro variable name as a rare one the value where it is coming from in my data set of my ovo you carefully observe so the data is coming from the variable called percent a variable called sorry the variable here is called count the variable i need to get this value from the count variable i'm assigning that value here so i'm writing it c c-o-u-n-t count semicolon and run that's it now is it that macro variable really created or not i'm still doubt so what i'll do is i'll check it by using percentage port ampersand sign i will use it var one you will use it semicolon that's it we are done check that will it work or not executing it done go for the new value it is saying symbolic variable name must be in a letter or argument something if i miss data quotation sir quotations bad one and single quotes huh i don't think so is it a quotations is a problem i mean in the i mean in the powerpoint we saw single quotes but i don't think so let's see okay yeah no that's not a problem here okay we taken a variable from self.class first of all is it over dataset is available yes it is available we wanted the data only sex equal to female yes we are taking it calls input var1 and count okay what log is trying to say and let's see try to filter out huh the thing is you write it check in your log what's sex variable what where one is printing what is the wear one yes it's printing you see that in it is printing so there is no doubt in that so what you have to do is you directly pass your value ampersand okay you directly write it ampersand var one you write it here okay this is your for female data then you do the same thing for your mails also do it directly you write it the variable name saying it as a mail and then make it a new variable name called to and in your data also you check it it should be to your writing data and then you are writing it male so within a male information you are writing it the value as ampersand bar two got it so now we are done so if you are running this step automatically the titles will be ready according to your data check it females are 18 members our males are 20 members in your data but why it is taking this much of gap 18 to 20 because even though i have given very near only but why it is taking by default calls import it will take some space you try to change the function to as a call's input to calls input x so that it will truncate that particular spaces and now you can see that the values perfectly aligned you see that is it properly aligned now so females are 18 and males are 20 you are getting it now what we will do is imagine if i try updating a data with a see i earlier what happened i doubled the data with says class multiple times i'm just trying to do ss data set only one time now that means only 19 records right now we have it check that is the title is going to be updated according to my 19 records or not see now we have about total 19 records is the title is saying still the 38 records or 19 records what it is saying no it's updated it's updated right so that in future if i update it my data set suppose imagine in this assault class i'm writing it in multiple times saying it as a says help that class one time and again cesar.class and another times has a class another time how many times total 3 19 right check that the count is applying as a 3 19 huh check now is it getting created yes is it getting updated title automatically good yeah yes that is the use of calls calls input is created yeah i will explain calls input will be created you can derive that variable within a data set level that's a purpose yes madam please continue asking something i'm sorry sir so why it's a the difference which code is making difference could you please explain one more time please like which one is giving just 19 which which one is giving the three times 19 and the what gives like num line number nine and data x and percent a what does it give it's so we're putting any order could you please explain okay so actually what happens you know uh in this particular data set what we have it right now it is taking it the data from the self.class how many observations we have in this data set 19 19 records so what happened [Music] if you given a fixed title as it right now like earlier i have shown right if females are 10 members and males are 10 20 members something like that if i write it it's a fixed one you no need to you need to update again and again whenever the data gets changed right in future okay so that's the reason why you written a calls input method to derive that particular variable and so that now we are calculating it suppose imagine in future if i'm writing a self.class let's see i have a uh around 300 plus records we have it in future automatically is the title values is getting updated with the same or not you want to check it it is just i'm just duplicating the data because is that particular program is properly working or not i want to check it understand i duplicated the data how many observations we have it now 95 records we have it yes in my data center yes 95 records if i'm passing this 95 records containing a data into my data set is this particular title is getting updated or not i want to see according to the 95 records right it's supposed to be it's supposed to update right it's supposed to update when it is when you have written a program correct with calls input method you see that 45 members of females and 50 members of males perfect got it yes sir thank you yeah so when we will use it in real time you know when we have number of people participated in the clinical trial you will write a program and you produce the output okay the n and you will generate it but in future what happened the clinical trim team will always enter the data into the databases so that what happen when you run it now today you may have it around 20 members of patients which is there in your data maybe in future you will have 200 patients also there is a possibility so that you can't go and change your program there according to the title according to the data right so we have to write a program in such a way that the title should automatically updated according to your data of inside this is a use of statement calls input we can derive the variable which is in inside of your data set and we can use it whenever it's required i hope it's clear yes no mary it's clear to you clear to remaining yes yes sir thank you yeah so yeah please continue like uh in this case we are only using percentage put uh just to print the value right and there is no i mean we are not creating the macro or something in the no it is just for to checking is it macro variable really created that value or not before using it just for checking purpose understand it is not that we are not doing something if you are removing this percentage port sign in your data it's still okay it is not mandatory okay what value you hold it you get to know that then only go for percentage put otherwise you no need to write once you know that the program is working perfect then without writing a percentage potential itself you can continue with your program editor yeah okay that is just you letting know that what values you have it for where one what values you have it for where to okay okay thank you and so one more question about the little bit if you go up there is like non-print first time when you wrote if you go back to the program is it now there is no print option i have written it very first time then you're wrong i have written this option in our proc frequency class itself no print means we are forcefully telling proc frequency not to print output by default what we know uh by default what happens you know every time it will give a print in your output window right every time yes but i'm forcefully telling it sas not to print that because i'm not interested on my output i'm writing an option called no print so my question is sir yes if we put now now print and out so the if we don't put non-print no print will it create uh still creating sus uh i mean work table the oh table that is actually when you try to learn the macros it is a very compulsory uh that definitely you are strong enough on your base as programming okay so yeah that is for must you are not confident on base size then bases program will keep you back every time so that you don't know the syntax first of all how to write it then how you will macro ties it so that way it's very dangerous one so make sure make you make sure the base test programming be strong enough i think i have explained the same program for you it's already there in our powerpoint presentation so i have done the same for you i have taken it the data and i'm just assigning the values yes done right and karl's input is not always deriving it if you want it a new variable creation also you can write it calls importer you do one thing calls import calls input x you try to create a new variable name called uh you can write it in a variable name you can write it um abc equal to as a new macro variable name i want to give it as a 10. wherever you want to use abc you just make it and execute it and run it now you see that what value you have it in abc percentage put ampersand abc semicolon that's it execute it run it now perfect percentage of misleads ah it's not ampersand it should be percentage go to log perfect 10 you are getting it right the value yes sir yes you will get it come here the next one is this is one way we have learned what is that sql into method sorry one is with your calls input method there is another way also to creating a macro variable that is a sql into method how we can do that very simple you try taking the same code as it is into sas what i'm doing it here as we already know the sql procedures so meanwhile my outputs are very huge i'll try to just is it deleted yes i hope it is yeah so now you are pasting it so what you are writing it proc sql no print i don't want to print an output window by default sql will give a printing output now what i'm doing it i'm telling this as select count 6 that means i'm counting variable based on your sex variable wise into into is the statement is used to create a new macro variable so what we are doing in we are doing the same step like your calls input method but calls input method is our uh what i can say it is if it is in the data step method and then only we can go for it but imagine you don't have any data step you are writing everything in sql then how you can derive a variable uh within the data set containing macro variable by using sql statement yes we can write it sql into method sql you write it into n1 from n1 n2 n3 that's up to you you can write it anymore that's a that's completely depends on you so select count of sex into n1 from self dot class where sex equal to male separate variable select account sex into n2 from sasebo.class where sexy equal to female that's it can be able to create it instead of calculating frequency and all the stuff and calculating calls input can we do this method is it easy yes or no yes yeah you can do that the same thing what we can do with the calls import here also we can do the same and execute it and now you can see your new variables will have the values of one is ten members in your data n2 means it is your nine members in your data perfect what you have done so why are we using the colon so colon is the sign to derive this is the syntax madam the colon is assigned to derive a new macro variable as n1 to derive a new macro variable as a n2 okay thank you good okay creating a macro variable with loop as we have already know that we have land width percentage led we have done with calls import and we have done with sql into and this is another method is called a loop how we can do with creating a macro variable with loops let's see that also come here i'll just remove everything it's a use concept okay now what i have done percentage macro i have written my macro name as a repeat data equal to abc do i one two three first of all when you are writing any loops in macros within the macro mainly not outside the macro if you are writing outside macro you don't need to use any percentage science or anything that we have already seen throughout our basis programming but what we are doing it right now percentages do that means if you write a macros if you write a loop within the macro definitely percentage sign is required if i'm not giving it it will throw an error message the loop is in open codes or something like that so make sure to write it percentage do i want to to also you should have percentage three that means i value one two three times i am running it x equal to is a new variable i am creating it that means x means ampersand i am writing it that means first time the loop is running the y value is one so that the x one variable will be created the value is test1 in that case second time the loop is running x2 test2 you will create a new macro variable x3 test 3 will be created percentage end you see that you are trying to create a new data set called abc yes go to my work library so there are many data sets let me delete these data sets also so i'm just writing it delete or just deleting the data sets is it not deleted because right click yes sure i am interested to delete all okay that's fine so now you have deleted percentage repeat so now what we can do is let's see the abc data set is not there i'm freshly creating it now now you see that abc data set wise we have a three variables x1 x2 x3 and you have three values also test one test two test three got it what you are doing it this is the macro variable you are creating it by the do statement you can use this macro variable wherever in this particular program within a and statement before the understatement only possible you can't write it anywhere else up apart from end within an end statement before that you can write it anywhere you can use it understand you can write it as a separate variable or separate condition or anything you can declare it what you have done percentages do i one two three times you are repeating a loop x ampersand with i that means we are concatenating x value with my ampersa i value i value right now it is one so that x one so that test is the variable and i value is one test one you will get it test two test three tests for like that you can create unlimited variables by using this one this is a another way of creating a macro variable by using sql sorry by using loops is it understood no yes so we're creating the variables just fine to include some observations like how [Music] uh suppose tell me what you want it so observations in the sense like in the macro we are using the data step right like data abc up to run so like in um instead of giving input and the variables names we have given test one test to test three what if we include what the value for test one test two and this three something like that you can write it no condition if okay you write a condition saying it as a if ampersand okay if ampersand sorry x ampersand i what you want to write it tell me do you in any condition you want to write it if it is the test one then you want to write it something like maybe just the value of 100 or something oh yeah you can write it so if if this is the value so x1 of your test one then what you can write you write it as a val equal to 100 make sure you write this also in a quotation sir percentage if days then [Music] it should be in a quotations yes you check it now the thing is while creating it let this variable created freely because before variable creation the if is working there that's a problem you write it now percentage if and person is then and you write it now it will work [Music] uh because is it like a val variable should be created first time is it okay that we can directly no no the problem is what happens you know the macro variable execution happens before your if statement yeah okay [Music] so you're writing it so your interest is if it is a can we do like uh do test one equal to 100 semicolon just in one step something like that so like for a test one if you know that if you write it it's a gain automatic so you're not using automation understand no no test ampersand i that we can't use right so do you can write it percentage do okay so then you are writing it do uh ampersand x ampersand not ampersand just write it down within a same condition itself you can write it right what is the problem if do one two three you are writing it x equal to test and you are writing it write a one more loop here that will solve our ratio now hello sorry yes please so this is the last class in advance last topic but not last class in the sense hello oh you are expecting some value if test one is this one huh so you think that first of all you want to write it 100 or some value like this directly is it something like this is possible yeah like what i understood is like x ampersand i we are giving and we are giving it as the test person i okay sir so x uh is it only for variable creation or can we even include uh observations in it so that was the uh wherever you want that one you can use it suppose imagine uh you want that uh value as a hundred plus okay ampersand uh i you can use it ampersand i you can use it in this case what do you is it okay okay so will it be coming in the variable name or that you see that x1 has a one at one x2 has a one or two x3 as a one or three got it that means it's summing up if you want it you can do anything with that you can write any logic you can build it on top of it you want to multiply with it or you want to filter that or you want to use that as a substitute function also suppose imagine you have a data okay so you have a data you're writing it as a a equal to imagine you're writing a equal to in a uh substitute of sub sub substitute of my value i'm writing it what i'm writing it uh instead of sub-saur write a scan so that no you will get a good result scan i need it what i wanted my my name is naidu i'm writing it hmm so what i wanted comma ampersand what i can write i write it i can write it right ampersand i and semicolon so what happens now yeah first ampersand i you try to write it here also okay so that first value first word will be what second word will be what in your data scan the word not letter substar is looking for a letter scanning character okay first so that what happened yeah so my name is welcome you see now my a1 has a mi a2 as a name a3 as an ease you got it right yeah that is your purpose actually this is just not for any standard rule we need to use it like that so then you can use it based on that you can build any logic maybe i need it for this value for scan or maybe i need it for something for multiplication i need it something uh to wherever you find a missing value i want to put the i value there you can write it in any logic based on it yes that's why thank you so much yeah yeah thank you good so that is you are creating a loops with macros and now we will move on deleting user defined macro variable we done with all the macro variable creation as of now you know that percentage is let personal local percentages global percentage calls input and sql into method what else last one which i have said do loop method also within loops also we can able to create a macro variables now if the macro variable is created but now what happen if i'm running it the macro variable which is already there it's working in my computer so let's see if i'm writing it somewhere some value the as i a suppose if i'm writing it any macro variables i have created in my sas i want to know that var 1 macro variable what value i have it in this so i can write it by just mentioning it percentages put what value you have it for ampersand var1 do you remember even i do don't know that so what i can do is if i'm just clicking on it and it's giving it the message is saying it as a ampersand var v a r not v a there is no variable such a variable created earlier let's see ampersand where is the macro variable created or not execute it run it go to the log and now you can see that it's a 45 you are getting it yep just hold on a second okay so now you will have a value of 45 so if you want to delete that macro variable you can write it by using an option called simdel so what it will do it will delete that macro so right now i know that i don't want this particular macro variable to be used anymore actually what happened you have seen a global options in our titles right once you have activated the title it will be continued for your next also if you want to stop the title you will write a statement in your global option title with semicolon that's it it will stop there but here what happened this is your you want to continue you don't want to continue then write it as percentage sim del simdel and write it the variable name so what is clearly saying which variable to be to be deleted is it a var one to be deleted write it where one semicolon and run it that's it execute it so that variable is deleted now i think it successfully deleted yes now you check for ampersand where one value is still it is exist or not check it by using percentage port is it with you i don't think so yeah it's saying the message the variable symbol is reference not resolved because we deleted that variable there is no such variable now so that you won't get it anything clear good so that is just for deleting observations in a data right so this is a placing a text before a macro variable okay so now once we've done this so deleting a defined macro variables so which is already there the macro variable is already there and if you want to delete it yes you can delete by using an option called simdel and we have done this now we'll move on to the placing a text before a macro variable reference how we can place a text before a macro variable difference and let's see what happens okay i'm just writing it here percentage letter visit underscore id equal to 4 percentage letter id equal to 50 so what is the difference i'm just creating a two macro variable names i'm writing it percentage let visit underscore id equal to 4 percent then let id equal to 52 macro variables i have created now i'm creating my data set name as a damp data your data set name naido ampersand visit underscore id and ampersand id i'm just giving it set says help.class what happens in if this particular program runs so let me do one thing let me copy the code what i have written here and paste it into sas and pasting it here so data my data set name i have written here it's in a wizard underscore id as a 4 personal let id equal to 50 i'm trying to write a new data set name as a i do visit underscore id with i am percent id if i run it or can you just tell me what happens if i execute it yes 450 naidu 450 is the data set name you are expecting that's the right answer if i execute it yes you can get it a new data set in your library naidu you will get it from here ampersand widget underscore id from my data set which is already there here you will get it for and id will be the 50 so that naidu 450 is a new data set will be get created so what we are doing it here we are placing a texture before my macro variable the place here is the texture an id is the text i am placing it before my macro variable yes that's perfectly working fine without having any issues so the placing a text before a macro variable so that's perfectly fine now what we will do is placing a text after macro variable we will see what happens i have a data with me data trial is my data set name input statement patient id visit one visit two with three and visit four cards here statement and there we are providing some visit information everything is perfect i'm creating a data set name with the name of trial but now what i'm doing is percentage letter p equal to visit so then if i want to use it let me just write it in sas there i will explain first i will explain this program you see here what i'm doing is data my data set name trial visit one with two widget three and widget four i have it so i'm just creating it a new data set name perfect new data set is guest created with the name of trial and there you have a pid visit and visit two visit three and visit four information it just got created perfect now what i'm doing is i'm writing it percentage let ampersand p equal to visit i'm just creating as a new macro variable so i can directly write it as an ampersand p means visit one means visit one ampersand p means visit your two means widget two i'm writing it so that i'm placing a text one is one text and two is the text i'm placing it after my macro variable i'm thinking that it will work fine without having any issues let's say percentage let p equal to visit and proc frequency data equal to a data set name trial table statement ampersand p1 and as well as ampersand p2 execute it and run it that's done so new data set when you are printing it so when i'm trying to execute it you see that the message error message you will get it and it's clearly saying that apparent symbol reference p1 is not resolved or p2 is not resolved why sas will think in this way do you know because we didn't assign values for p1 and p2 you're right we didn't assign any value directly as a p1 or p2 right how we assign the value as p means it's a measure ampersand p means visit and ampersand p means visit two but what is the problem earlier when i'm writing it naidu 450 it worked right and i do is the text i'm placing my before my macro variables that's working well but why in this case it's not working maybe text is placed after the value ah so you're right placing a text after macro variable difference when you're calling it so definitely it should be separated with dot this dot refers to a new text line you're performing it so ampersand p means it's a visit one means it's a visit one ampersand p means it's a visit two means visit two you're trying to create it and execute it and now you run it and now you can see that a new data set it just got created and there you can see visit one with values of 20 and you will get it 22 visit 2 with 40 and 45 will get it perfect yes clear questions sir yes please yeah like uh sorry sir um my network there was some network issue would you please just explain one more time so uh no matter it's already there in uh recording video maybe just go through it here i'm not explaining anything new so here what i have done is i'm just placing a text before a macro variable to placing a text after macro variable concept i'm explaining earlier it's working well and placing a text after macro variable definitely we need to write a dot then only it can be read that's the only difference okay okay yeah thank you son yeah yeah thank you so now we will go for a classic example with this we'll see here so what we have it here in this particular program let me just talk about that huh so what i have percentage letter libby equal to sas user okay i have written i am directly writing it proc sort data equal to sasha dot class i am trying to create a new data set name called ampersand lib ampersand leave me sas user dot class i'm interested to save my class data set in my sas user library that's what i'm trying to do if i execute this piece of code what happen will it save the data set of your class in sas user yes or no no sir reasons like uh generally the two level name is library dot the data set name so but after the variable if you are going to place a text you need a dot right so i think there should be some dot yes you're right so what what happen if i run this particular program you know execute it go to the log and log is clearly saying work dot sas user class as a new data set is it correct is it what we are expecting no no we are expecting a new data set here as a nothing in work library in sas user library we are expecting a data set called class but unfortunately what happened it is reading as a sas user class do you know why it's happened because you are placing a text class is a text you are placing it after my macro variable the macro variable ampersand libby is my macro variable there you have a sas user one dot is required but where is another dot to mention the library one dot is for placing a text before a macro variable where is another dot is for placing your text one is for library difference another one you were for macro variable difference you need to make sure to write it proxy data equal to subject class out equal to lib data so two dots you need to mention based on the class and by sex variable wise and execute it and run it that's done now you created a new data set with the name of lib so go to your new library or even you check your log also log is clearly saying sas user.class is a new dataset created go to sas user library class dataset is created perfect so what you need to remember always placing a text after a macro variable definitely dot is required placing before text so placing a a text before a macro variable does not require anything placing a text after macro variable definitely the dot is required right so you try remember that so that we have already done in this particular slide let me just go through this this we have already completed placing a text before macro variable placing a text after macro variable we done so now appending a macro variable how we can append it so we can append the macro variable by using ampersand sign ampersand means we are calling that particular macro so you can think uh the double ampersand size is a special reference resolve to a single ampersand when you are giving a double ampersand sign set somewhere it will work like a symbol amp single ampersand sign here is an example now i'm writing it what happened percentage let dsn equal to mark it and personally let n equal to 10 percent they let dsn equal to 10 and my percentage let market equal to 10 so that what happened you have a value first one dsn as a market n is 10 and dsn 10 is world and market 10 is new something like that i'm giving it let's see what happens i'm writing it the value of personal letter ampersand dsn and ampersand n what resolves it the macro values it will resolves to ampersand n means it is 10 it is 10 ds and 10 means what is the value market 10 so market 10 you will get it dsn 10 means here how it first of all how it is resolved first dsn means it is market you will get it and ampersand n means it's a 10 so that market 10 you will get it you see this what happened am percent ampersand dsn what is the value how it will be resolved as a like cast but the text you should take only one ampersand instead of as i already mentioned two ampersand sign will be considered as a one ampersand sign so in that one ampersand sign how it will be consider ampersand ds and n what is the dsn ampersand dsn and ampersand n so ampersand that means it's a ds and 10 you will get it right so ampersand dsn 10 what is the value oh that is old yeah you will get it old now you see that ampersand ampersand ampersand three amperes and signs and dsn and ampersand n and now you will get it how the first resolution will be it will be resolved as a ampersand dsn is going to be market 10 it is just first of all you will get it mark it with 10 you will get it ampersand market 10 is resolves to what new you will get it the value is got it what i'm saying when you are combining the variables together you will see that all the macro variables are resolving ampersand dsn and ampersand n so first one is market 10 and ampersand ampersand dsn and ampersand 10 first will be 2 amp sign will be resolved as a single ampersand sign there so ampersand dsn 10 is going to be your old and now last one is ampersand market 10 so that what happened 2 amperes and sign will be resolved as one thing so that dsn 10 dsn 10 is going to be so dsn 10 is going to be your market 10 so you will get it so sorry market 10 you will get it market 10 value your final result resolving it as a new here so if you want you can try it in your sas it will work in the same fashion excuse me sir yep yeah so i have a question with the last one the final row with the triple ampersand actually ampersand dsn ampersand n is marked 10 right where the first line so uh here we have double ampersand and then market sign will be resolved as a single ampersand sign so what happened at final last you will have a double ampersand sign with market 10 am i right correct okay yeah double ampersand prime will be considered as a single ampersand sign that you already know that so that single ampersand with market 10 it's going to be your old okay you're new actually yeah new okay sorry i've got it it's a new yeah you're right yeah yeah thank you thank you sorry i just have one last query so if we put percentage put and followed by this combination the last combination so are we going to get new in the log or the first can result in sorry okay we'll do one thing hmm okay okay we'll try running this value whatever value we will get it in our log percentage put the value you're getting new right perfect yes yeah yeah okay fine thank you good so now we are done appending a macro variable sometimes when you will use it in real time you know percentage let dsn equal to dnb a percentage let f equal to xls somewhere like this so if you want to write it dnb xls so you will write it ampersand dsn and ampersand f that will be dnb xls or sometimes you want to write a one ampersand dsn one as a dnb one you want to write it what you will do ampersand dsm dot you will write it because i am placing a text one is the text you are placing it after macro variable reference one is required and so it should be dot ampersand dsn.1 so that dnb dot or dnb1 you will get it or else if you want it dnb dot xls so the dot is required for your xls extension then you need to write it two dots that we already seen that right earlier ampersand dsn means it is your dnb so dot is required because you are making a values is excellence you are placing a text after your macro variable one dot is required another dot is for your xls extension so dnb dot xls you will get it i think this will you already know that i no need to mention why we need to mention the two dots yes agree no yeah good macro parameters so in macro parameters is nothing but macro parameters allow you to define a macro variables without using a percentage let statement as of now what happened what we have done whenever we want to use any macro you define with the percentage led calls input or maybe percentage do statement percentage global percentage local so these are all the options we used to do it as of now now we are using a macro parameters it's not required to write it any percentage related science or anything we can directly use this particular macro variable macro parameter as used to pass the value or a text string into a macro that's the first thing what we do so how we can use this particular macro parameters mainly in sas so two parameters we have it one is types of parameter positional parameters and keyword parameters so what is the difference between these parameters positional parameters when we will call it as and keyword parameters macros when we will call it as a positional parameters in the sense so positional parameters drive their name from the fact that r defined using a specific position on the macro that means see it's a position positional parameter that means it will work always based on your position if you are giving a first position the macro will work on first position on the second position second position let me see some of the examples i'll show you in the next slide when a macro is executed the value passes to the same position in the macro column yes that also i will explain here you see percentage macro srt is my macro name i kept it you can give it anything that's up to you x comma y comma z is the three positions i mentioned it because when i say it's a positional parameters x is one position first position y is the second question and that is the third question proc sort data equal to ampersand x whatever you use it in the first equation that you are calling it out equal to ampersand y in my second position whatever i'm just using it i'm just using it as a ampersand y by which variable wise you want to do it ampersand jet and run it proc print data equal to ampersand y wise whatever we have given and run percentage mnd let's say what we will do is let me just copy this particular program into software and run it i'm just writing it percentage srt is my macro name within that macro you have x y and z so three positions we have it this is your positional parameter macro this is my first position second position and third position so everything you are performing uh you're perfectly writing it here there is no problem perfect so if you want to call this macro what you will do percentage srt you are calling the macro x value it's a sasser.class y value it is naidu i'm trying to create a new dataset name as an ido and jet value you are trying to write it as a sex that's it you execute it run the program you execute it that's it the data just got sorted based on your sex variable wise and you created a new data set name as uh here into your workflow library in i do perfect it's working what you have done i've done nothing i just mentioned it positions wise exposition it's a self dot class y position it's an ido jet position it's a sex let's say if i change the positions what happens if i'm changing it the positions from here instead of says help dot class i'm trying to write it the first position as a uh i'll directly write it as a sex i will write it here will it work first position is sex second position is naidu and third position is based on my sas help dot class tell me will it work it doesn't no it doesn't work right how it will be resolved proxart data equal to your data set name sex out equal to new data set name naido by sasel dot class wise you are sorting it will it work it won't the limitation is you have to make sure to use your parameters that your x and y and g is the parameters to be used in a order wise only first of x then followed by y and followed by z you declare it then only it will work if you declare it in different way then your program will not work making sense yes or no good yes sir good suppose imagine i'm not creating a new data set name called naidu in this itself so what happened if i'm not using any new data set name and i do here what is going to happen will it throw an error yes because there is no enough information for it to is it giving an error after that out equal to i'm just giving ampersand y but i'm not giving any name for that expecting sas is expecting a name so definitely throw an error got it so we need to mention that particular value and then only the issue will be get solved perfect this is your positional parameters macro and that example which i have already explained calm down now we'll go for keyword parameters macro what is keyword parameters macro what we can do with keyword parameters macro you see parameters may be designated as a keyword percentage macro statement so that means you will write it percentage macro as same then the only difference between your positional parameters and keyword parameters is the important line yeah yeah why my voice is breaking yes we could be able to listen anything can you hear me now cleo no yes perfect so the audio is restored now okay so now what happens you know passing a keyboard parameters macro you see that how i can able to pass a keyword parameters macro it's same like earlier the only difference is we will write it equal to a equal to b equal to c equal to options equal to or something like that so that no it is not mandatory that i need to mention based on the positions it is based on my names if i'm writing a equal to at last also it will washing it you you are losing me because of internet issues so in this particular positional parameters macro sorry keyword parameters macro so we have to give the positions you with me can you able to hear me now let's see some positional parameters macros we are just using it positional parameters wise percentage macro and you can give a macro name anything the only difference between positional parameters and keyword parameters is positional parameters you will mention positions based on the positions wise x equal to y equal to z equal if you mention it you will stick to the positions but we are coming to here it is not based on the position it is like a equal to sign a equal to b equal to c equal to options equal to or something like that we write it so that is your push uh keyword parameters what we will do is we will try taking the same example and let's see how we can able to do with it first i'll try to write a macro in my sas then followed by i'll try writing it so now what happen percentage macro keyword equal to a equal to i have written b equal to have written c equal to options equal to i have written see earlier i have mentioned x y z and all positions but now i'm mentioning it a equal to b equal to c equal to options equal to proc means data equal to your ampersand a data set name ampersand a and options class statement b and var statement ampersand c you are writing it and run so now you are trying to create a new dataset name as class perfect now if i mention the positions how i need to mention a equal to self.class b equal to you're mentioning it the value is going to be sex and c equal to age and options equal to n and mean you are giving it so it is not mandatory that i need to mention based on the positions wise you see according to my declaration i can change my position to here to anywhere okay if i change my position so instead of having lost i can give my positions here it's a top itself i can give it the only thing is just mention with the coils is it fine i can write it like this also options equal to n and main a equal to sub dot class b equal to x and c equal the difference is we are just maintaining with equal to signs okay let's see if i execute yes please sorry yeah like after the percentage yummy and we have to give the macro name key the thing is if you can give it r without giving also it will work the thing is let's see if i'm giving it you are telling that specifically which macro you are ending it okay but when you are not mentioning it when the problem will happens you know you have written a macro in macro macro in macro like a number of macros you have written which macro is started and which macro is ended where is difficult for job when you're writing and sometimes so in that case you have to make sure to mention the macro name otherwise if you are not writing the macro name also still it will work it will not throw any error to you this one with the keyword i'm writing one more macro inside of my this main macro if i'm writing it percentage uh macro if i'm writing it i'm writing it as a a so i'm writing it as a i'm writing it something or maybe don't write anything and i'm writing it again mn statement percentage m and tell me which macro is started where and which macro is ended where do you remember it's difficult right to answer it yes or no right so if i tell this particular macro is a then it is easy if i tell this particular macro is key wd so then it is easy right to mention it for one macro if i'm writing it if i'm not writing a name that's completely fine that will work but when the confusion will came into picture when you're writing macro in macro macro in macro which macro is starting where and which macro is ending where it's difficult then you have to make sure to write it a proper name then only it will work making sense i hope no need to explain more than that sir so we need to decide where it goes right like which macro comes first or it is a pro per requirement we need to finish the task yes you're right so when you're writing a multiple macros then we need to make sure which mac pro to be executed faster which macro to be executed last you can write it you should be in a position to take a call on that which macro you will write it as a main macro what will be the sub macro inside of it understand let it sir yes yep so this is your positional parameters macro suppose if i'm mentioning it positions wise if i'm writing at the bar statement if i'm not mentioning it here i'm just writing it where c equal to i'm not mentioning anything will it throw an error now yes no it was done why it will not be enough earlier we have seen and you are not mentioning your positions what is giving cut it what is mentioning it so definitely so you have to mention your the value because when you're writing it where ampersand c when you are seeing ampersand c means you should have some value right i'm expecting otherwise how these particular things will work yes yes yes yes so now we'll try to do the same thing we'll try writing it proc means data equal to instead of means i'll do one thing proc sort data equal to your data set name i'm writing it ampersand a out equal to i'm writing it um b i think that's done let me write it one statement will it work again we have to give here uh something value let's see okay which one it will take all positional parameters macro and keyword it's giving it is it because of that it's not working check your new data set is created do one thing the work library already have so you are giving out statement but out statement you are missing out definitely it is not working what do you say yes yes it won't right it's throwing an error so it's not correct good so now we'll go over this is your positional parameters and as well as keyword parameters macro and this is how we can able to use positional parameters sorry keyword parameters macro to import your excel sheets also if you want it maybe you try to imp impute some excel sheet you want to write it let's see you're writing this program percentage imp is my macro name and a equal to you are writing it proc import your data out equal to your dataset name you're writing it work dot ampersand yeah you're writing it otherwise without writing work also it will work as you already know the default where it will save it will be saved in work library data file equal to the path of that particular excel where it is located and dbms equal to xls and replace sheet equal to which sheet to be imported ampersand yeah that means i will pass it in future what data set name i'm just writing it here so don't write anything dot or anything get mc code yes and run an imp i'm writing it if i'm trying to call it what i need to call ampersand percentage a equal to then i need to mention first of all the paths and everything i have to set it right first thing go to my sas classes day 6 and 7 there are many files in this let me go for my data one so here go to properties yes it's there with me i'll right click on it go to properties make sure to copy the path of this particular file where it is located copied then which sheets you have it in the data let me just open my sheets also i have to give my sheet name naidu is the sheet which is there then i'll try writing it the path of that excel file wait to be read because the path what its existing one is not correct i'm trying to read the correct one and you are just giving a path and here i'm writing it the sheet name is called naidu in a in a ido you write it semicolon that's it we are done this particular piece of code is enough to execute the data to be imported make sure that excel is should be closed before and make sure before running it there is one more sheet called uu also let me run that also later on so now i think i just imported yes the seat is imported with 120 observations containing a data is imported go there and check your new data set of your naido see all the data on that particular excel what you have it is properly imported into sas environment without have any issues making sense yes or no so now if i want to import it from another sheet can we do the same do you need to change anything in my program just to change the parameter instead of naidu i have a sheet name called you you you is a sheet name you try i take it and execute it i think we are done go to your new data set called you open it and there you can see that all the sheets information is imported perfect agree okay so we like we had this uu sheet it exists yes it's already exist in my excel sheet in my data one dot that's the reason why otherwise if your sheet name is called sheet1 then you can write it percentage imp and then in a bracket you write it uh a equal to you write it as a sheet 1 then the sheet 1 data if it is there in your data set will be get imported then you need to mention which sheet to be imported that's your data set name and that is your sheet name to be imported imagine you have a sheet 1 sheet 2 sheet 3 like 100 sheets you have it but all the android sheets you want to import as a different data set names is it possible understand what i'm saying so i have this data what i'll do is i'll try copy this data to my desk i'm pasting it on my desktop perfect i'm pasting on my desktop i'm changing something in this particular excel oh this is copied one yeah this is copied one so i'm giving it my name as a sheet one this is my first sheet maybe i need to write it sheet one this is my first sheet information and this is my renaming it sheet to this is my second sheet information perfect i want to import two sheets information without writing the program twice okay the logic is now okay i have a data and the security it's there in my desktop i'll copy the path earlier i have written the program twice to copy it what i'll do is i'm changing my program as this one i'm trying to import it let's see for testing purpose is it working or not first of all let me close my yoyo if i write it my sheet name called sheet1 is it working or not write it sheet one for my first sheet i think it should be spelling mistake you write it sheet one for my first sheet i'm writing it sheet two for my shaking sheet can i import two sheets check your log first of all i think i have executed everything select don't select anything yes you have a sheet one data imported sheet two data also imported but the thing is you have a hundred seats like this seat one sheet two sheet three like a hundred sheets can be able to import all the under seat once so that you will repeat this particular program hundred times how you will do anything is there any logic behind it a macro to call all the sheets ah all the sheets how we can tell that all the sheets what is the difference in the sheath the numbers only the last right cheat one sheet two sheet three sheet four like that it will keep on can we do using loops okay now u doesn't do do i equal to one to one hundred something can we use that particular procedure what we have used earlier so let's see with the logic you have built already do you remember yeah it's a do loop that's right somewhere you already landed somewhere calling a macro variable macro variable with loops yes you're right so try to use that same knowledge what you already know that you try use the same knowledge what you already know that okay try taking this particular data try execute this particular sheets will it work you have written percentage macro repeat that i called your data set name a and instead of that i am not interested to have my data set name underscore null underscore nala underscore because i'm not interested to have it my data set how many times this particular loop to be run this time two times only so what is the macro to be run which macro to random name this macro to be run how many times should need to be run two times we need to run this macro we are just writing it instead of ampersand what we will do write it now here perfect if i try to repeat it let me delete everything let me don't delete macro because the macro is imp macro which is already there it is created so now what i'm doing is i'm just importing it two sheets also will be imported yes this program is enough to import two sheets also yes or no yes yes yeah even also can be able to import it with the same method yes or no yes sir right so the only thing is the last digit you need to automatically get updated this is another way of using a loops middle with macros to run okay okay yeah yep good so we can repeat the loops like that also if required we can do that okay let me just go to our slides where we are positional parameters keyword parameters huh i think we we have done it here so now we have some options called yam print m logic and symbol gen options what is this m print what is this amlogic and symbol gen what we can do with it first of all you see in your log what is happening the macro is running what is the sheet 1 value refers to you are not getting it and understand yes or no what sheet 1 refers to how the value will be refers to what program it is printing to write this particular message is there any program how we get to know how the program is working in the back end if you want to know it how we can able to know it very simple you write it options m print let's see what it will give the message to you once you activate it's a global option again once you activated mprint option is activated now if i run my program now if i execute it go and check your log what it's giving a message it is giving proc import out equal to sheet2 data file equal to path and replace sheet equal to sheet2 information you are getting imported earlier do you see that no sir but why you are getting it because m print it will tell the source of the program to be printed after resolving your macro variables how the your program is looks like that will be printed in your log that's a use of mprint option okay understand okay imprint you already know that the program is how it is printing how it is working the macro variables are resolving and everything is printing perfectly fine okay then we want to know that um instead of so so what is get means oh my god get names equal to yes is default if you want to get the first observations as a variable names in while importing excel sheets you write getname sql yes statement you already know the syntax for your basis programming import of excel because i am not telling the program how to import excel sheets i already explained in your day six and seven how to import excel sheets into sas environment yes sir yes instead i recall yes yeah so now imprint is already activated once it's activated it will be continued for throughout your session but i am not interested anymore i written an option called no imprint so that it won't give any print anymore execute it now you are deactivating it that options anymore to run go to log so there is no imprint again now we will talk about what symbols and we'll talk about it okay you written no imprint that's okay you activated and you are deactivated s-y-m-b-o-l-g-e-n symbol gen what it will do execute it run it it's giving it let's see is there anything talking about the symbol gen is it symbols and what it resolves to it clearly tells that what macro variable holds and what value it resolves to when the macro variable i equal to first time it resolves to 1 then it will be resolved as a sheet 1 and here also it will be sheet 1 again when the macro variable resolves to 2 then it is going to be your sheet 2 and it will be resolved as a sheet 2 making sense what it tells us it tells us is that macro variable is properly resolving or not got it yes no yes yes instead of just program to be printed you are interested to see is that particular macro variable only printing or not you can get it by using these particular options that's a use of symbols and option one more called m logic i can't explain the m logic because i don't have any examples but i will explain in upcoming programs to you m logic also it will tell that which logic wise the program is right or wrong you can get it by using this particular m logic that we will see in next program so sir i'm sorry one more question that this underscore now on this uh underscore it is but because of that we're not getting anything output like like ours you are not creating any data set you see where is the data set name as an underscore null underscore because i am not interested to create a data set here understand what i'm saying no uh yes sir but what it gives like underscore null underscore what it gives it's not giving anything at the same time it is allowing me to run my loop loops we can write into only in the data step as all right am i right or wrong yes yes right yes sir within that i'm writing a data underscore null underscore so i'm creating a still data set but i'm writing it not to create any data set because of that now it's uh doesn't have to create one thing if you think that it's giving a problem directly write it percentage do one two two i this is the time and let's see will it work or not is that giving an error you know no is it not no right we can write a macros within that also without writing also knowledge the sign is used for when sometimes sometimes you don't want the data set to be created at the same time you want to use the loops and conditional statement defaults then conditions if you want to use it then you need to depend on uh null statement okay so all of this is for backing right yes how do you thank you sir okay so now we'll move on so this is the uh like a what am co m print is talking about and this is all about your m logic and symbols and option i already explained to you in detail so what m print is talking about and all so simple macro functions what we can do with this particular macro functions is percentage str when you will use this particular str let's see as we already know that what we know we are writing it the title okay while printing this particular program once it's imported i'm interested to print it as a proc proc print data equal to your data set name ampersand a title i'm writing it title i'm writing in a quotations that ampersand tl is a new macro variable i will create it i will declare it here and run statement what i am doing it in this i'm writing it a equal to i have written tlr also equal to i will return so that a equal to you already declared it here i'm writing it tl equal to i'm declaring it this is my student info something like that i'm writing it that's it so that what happened the title will be printed while printing the output in output window the title will be printed this is my student info yes or no making sense the title is printing okay so now imagine this is my student info comma off today if i'm writing it will it work yes or no should work yes it should right the comma is of my title not for my macro parameter let's see what happens now you will error a positional mara meters must be proceed with the keyword parameters and all sas will think that yes the comma what you have given inside is thinking that it's a new parameter as of today is it off today is a new parameter i don't think so no it's a title then how i can tell my sas to take it as a title instead of adds a new parameter detonation maybe or then this particular function will come into picture called percentage str i'm just using it uh-huh i'm writing it somewhere so do one thing you write it here percentages yes t r you are writing it and you write and write it one more bracket because this bracket is belongs to the one that one that's it now you run the same program will it throw the same message again yes no no i don't think this will work it should work yes now you can see this is my student info comma of today comma is there it's printing what this str will do it will minimize your sas functionality like a commas and all maybe due to some reason i kept the semicolon here because semicolon is uh like this i need required can we do this earlier comma that str can minimize your columns also semicolons that will be an end of statement it will read at the text when you are using the option called str okay okay okay okay but when the str will fail suppose if i'm writing it this is my student info off jan and feb i'm writing it can we do it like this possible we test here i don't think so okay the ampersand little take uh that means ampersand means somewhere macro variable is resolving go there you will see that every single macro variable ampersand is not resolved do you think it's an ampersand it's in it january and february right we are giving it not as a ampersand how we can get rid of it str is not solving the issue then you will go for nr str then you can go for it now to solve the issue so that it will solve your minimizing comma semicolons and as well as ampersand signs also you won't see that message anymore can you see yes sir sometimes i want to write it january and february in a quotations can we do that possible might be yes sorry using functions may be that way there might be a way to get rid of that for nr str will solve that issue let's see the titles are printing as it is without having any disturbance why that quotation so you are not getting it and at the same time you will get it some errors also this is my student on for onwards that will cut the data and it shows something like this it is showing i mean that means you started with double quotations you ended with a double quotation again you started with the double quotation that's what sas will think is it correct no okay then percentage b code your value and write it one more on that will this solo yes it will solve but what it solved what happened is you try taking everything from here okay let's minimize the ampersand signs one more time write it this particular function on top here oh my gosh okay let's see is it getting it the quotations perfect without issues right that means you can read the quotations also into your titles right i'm talking about that str nr str is the same you can write a sub store index scan anything you can write it so you can do already i think scan function you already know that so nrstr you are just writing it because you want to minimize it so you want to write it the second letter in your data something like that you can go for this example any of these examples which we are giving it here you can go for it then you will get an understanding of what scam function will do what sub-store will do what is index will do you want to see that you want to let me just explain one example a equal to smile to me that's a good one here okay percentage let b equal to substitute ampersand a one comma five what will be the value you are expecting smile because it starts reading from your sentence with e right that's what you are expecting let's check yeah subscribe yeah so b equal to what value you have it in my b i'm expecting smile smile you're getting it perfect right yeah good so the same thing you can go for another examples also which is already there here right it's making so what is the change you have done you used percentage substitute function directly on top of my values okay so when you can go for this one to my smile to me smile to me there is a comma once you have a comma generally what substitute will think generally scan so what it will do is personally let be equal to scan of ampersand a why you are giving an rstr because i want to minimize this particular comma to read it as just a comma instead of having a statement it is trying to read it as a comma so ampersand a comma 2 that means second word if you find semicolon what is my second word in my data after comma is it two the comment is me summary yes if you run this particular program if i run it percentage portal what will be the value if i run it it should be me yeah me you'll get it understand no yes yes yes sir thank you good so now go for evolve function what it will do is evolve function let me try to explain that also what evolve will do percentage let a equal to 1 be equal to ampersand a plus 1 what will be the value you are expecting ah that's wrong ampersand percentage port ampere cent what is the value ampersand b value what you are expecting to be one plus one two as you are human being we are calculating as say one plus one right what is wrong with that yeah two but you will surprise get it with one plus one only do you know why no because arithmetic operator it is just reading everything as a character when you are giving a equal to one as a character variable not as a numeric again ampersand here it's a character only understand character plus character what can we have a naidu plus sanjaya means some value no it's a still character value right it's the same thing happens if our if you combine our ages okay some age plus is then you will have some value but the character values will never have sums right that's what happens how we can convert this as a analysis a plus one now what you are expecting your value will be should be because you specified it to convert you can run it the value you want to see ampersand c value what do you want to see you will see the two in your log two can you see that yes sir i hope everyone able to follow me everyone able to follow me sir yes so what if what difference does it make when we write a person put c equals to ampersand c and uh like is it the same when we write person put ampersancy no what i didn't get your question can you just elaborate so earlier it was written person put c equals to ampersand c so how does it make a difference from this statement percent ampersand c percentage put is the sign is used to print the value right what c value you are holding it here and you are trying to print it yes sir so when we write percent put c equals to ampersand c what difference does it make no if you are writing equal to also it will be printed as a text suppose if i'm writing percentage c equal to you're writing some text as a correct it is just text it is not something uh what we are doing it okay it will be print as a text whatever you printing it blog it is just printing it now you will see it's printing it now you will see that is always to displaying your text what text you want to display it in log you can write it here at the same time you can able to print your macro variables also in your log if you want it okay thank you sir yeah so that's all about here so now we are done with our evolve functions and all so now we will go for another function called valve when this will be useful what is the difference between evol and cc valve so let's say you take this particular example let me just play in a software what we can do with it i'm giving it down the value as a percentage letter 10.34 10.12.23 and all i want to do the sum earlier we used to do the sum with percentage eval and all it's working well and okay so now in this particular cisa valve what we can do is so we have a value so we can uh like a percentage let a equal to ten point three and percentile at y equal to ten twelve point uh 2 3 and all if you want to do the sum with eval it won't work it will work but the problem is it will throw some errors or something like that what we will do is we'll try copying this particular values and let's say and we'll try to play with our software will it work or not and what exactly uh the difference is so now go to your sas you just open it so now you're just placed pasting that percentile at x equal to 10.34 percentile at y equal to 12.23 percentage letter uh addd is a new variable i'm writing it so yeah it is valid equal to i'm writing it so i'm writing a function called cc valve here so within the syst valve if i'm writing it let's see what happens if i'm using up to here ctrl shift with upper button i'm just selecting it this total and run it i just executed it so now if i want to know that what value you have it for this particular adc swell value when i try writing it and i'm just printing it here semicolon i'm writing it now you execute it go to the log what value you are holding it it is giving a value as a 22.57 perfect so that's correct only so according to my data when i'm just using it it should be so 22.57 you are getting it perfectly fine i'll try doing the same thing with eval what happens we will see when i execute it i just execute executed and go to the log it will show a message saying it as a a character operand was not found or percentage evolve function and all because when you have a values with the decimals we can't be able to handle it with the percentage eval so we need to depends on valve so now you can see that the difference between uh add evolve value add cc valve value you want to just check it in your log what values you hold it it's giving system value we have it as a 22.57 as we are getting an error for evolve function when you are calculating for decimal values it will throw an error that's the reason why if all values are missing c several values are working because you have a dismals so one thing is very very clear if you want to use uh eval the scenario is definitely it should be integer value so if you want to use for cc valve so you can use integers also and as well as without decimals also suppose imagine if i'm not giving it as a value x equal to 10 and y equal to 12.23 and let's see will this work out without giving any errors you go to log and you see that it's working 22.23 it's working there is no doubt in that yeah without having a dismal also valve will work but evalu definitely uh if you are trying to handle the dismal containing values with evolve it won't work you need to depend on the function called valve that's the difference between your eval and valve what we have in our sash and so sysfunk i'm just using it here yes please continue yeah like uh why is it cis event f sir actually like uh what's the reason maybe the floating values i think i can say f means okay yeah thank you yeah yeah thank you so we are coming to here uh sysfunka so what is this funka so what it will do it allows you access the macro processor to most of the data step functions in several scl functions so the solution for a common macro task can be a simpler and more efficient way so generally when we write a system what is the use when you want to calculate a mean the mean will not work directly so you need to use a function called sysfunc so then you will use a mean for that suppose imagine i have a value ah so i want to calculate a a mean of your this particular value okay so what i'm just doing it i'm writing it um i'm writing it percentage let percentage letter i'm writing it abc equal to if i'm writing it i'm directly writing it mean of my ampersand ampersand x comma ampersand y will it work yes or no so will it throw an error or anything we'll see first of all if i'm writing it mean function i'm directly using a mean function i'm trying to give it so without any errors it's worked let's see what we can do is if i'm writing it um percentage put ampersand abc what value you will have it is it a mean value you are going to get it or not we want to see that abc wise is it mean getting it here possible you're not getting it right so as it is the text values is printing as it is okay instead of mean i'll try to do with percentage mean will that work go to log the percentage mean it's showing invoking macro mean function so mean will not work in this case what you have to do is you try using it one more function on top of it saying it as a percentage s y s f u n c cis funk you try using it sy yes cis punk huh so syspunk and percentage mean or something like that let's see will it work execute in log i think you for a mean function you don't need to write a percentage sign you remove it percentage mean and all syspunk you are using it 11 point 115 you are getting as a mean so that means the function if i'm directly using as a mean it won't work you need to use a function called extra thing is called sysfunc then only the general functions will work on your macros understand so the general functions if you want to work on macros you try writing it a function called some case like up case low case prop case by using percentage of case by by using percentage scan by using percentage substar those functions directly will work certain functions like this mean function and aggregate functions will not work so definitely you need to use sysfunc then only it will work on your computer otherwise it won't that's happening here so it will just check it will check that particular system value etc uh allowing that particular regular functions to work on your sas software like that what functions we have if you want to open the data set you can use a function function called sysfunc open it will open the data set if you want to close it the closed dataset also will happen with this or else you want to check that that particular dataset is already exist or not by using an option called you can exist and rc is another it returns a character attributes in a data set so what character values in your data set you want to go for it yes that values you can get it rls only numeric attributes you want to find out you can write a function called att rn so that it returns a function called it uh returns all the attributes of your data set values so like a numerical values only if you want to get it you can get it or else the variable type if you want it the wire type is it returns the variable type is it a uh what variable it is a character variable or numeric variable you can get it and returns a variable name suppose if you want to know that what variable name you want it in that particular data set you can go for it what i will do is i'll try to explain one or two functions in my next slide you see that what i'm just doing it in which scenarios we try to use it so let me just take this particular example i written a complete macro for this so that what i can do is i can use this particular functions how it will be useful so what i wanted i want to print if the data set is available in my sas user library houses then only i want to print that particular data set in my output window otherwise i want to put that message in my log saying it as a this particular data set is does not exist how i get to know that the data set is already exist or not based on the particular condition i'm applying a function called exist what i'm doing is percentage macro check ds is my macro name i have written within that i'm writing my macro value as a dsn so i'm writing it a b c instead of that you can write it anything called dsn percentage if sysfunc exists ampersand dsn if that particular data set is exist it will check in your work library or maybe wherever the library you are providing it it will check on that particular location if the data set is available into that location then do what it will do it will print that particular data set if else that particular data set is not exist then it will try to print it the message that does not exist so now i want to find out is it a sas user dot um maybe let me just check it is it abc data set is available or not i want to check it actually abc is not there that i know that so let me run it if this particular data set is already exist in my sas user library with the name of abc or not if this is working then i want to run a set of macros on top of it understand based on this particular condition when this particular macros should work if the data sets are available then only give a trigger or maybe take a launching point to run the macro so when it will happen you know in real time so client is saying that maybe tomorrow end of the day you will get the data sets available based on that your program should work then is that particular data sets are available or not how i can check it i can't manually go there and check by tomorrow by the same time automatically my program take care of that particular job how i can tell to my sas by just using an option called exist if that particular data set is exist then try to run this macro or if the data set is not exist trend don't try and run this macro or something like that we can tell it by using an option called exist yes i just checked it and it is giving a message the data set sas user.abc does not exist that's what it's giving it okay go to my libraries in sas user class data set is available let me tell that to my sas is it a class data set available if it is available what it will do it automatically print the output and output window see it is giving output in output window that means the data set is already exist and my macro is running and my macro function is just print my output in output window it is printing it based on a single condition checking you want to trigger a macro or maybe you want to run a macro so that is just like a triggering point when this particular program to work you are writing a condition understand what i am saying yes no able to follow me yes right so exist yeah like in the powerpoint i saw exists as the word and here it's exit so it either ways it will work or is it only exists only i think that i said all right yeah it's just like this thank you thank you for pointing out yeah thanks yeah so we are writing an option here called exist so that it will try to exist that particular value then it will give it otherwise it will run for another thing if you want it again so if you want to open the data set if it is already open the data set then you can open it or else if you want to find out how many observations in that particular data set wise so if you wanted to go for the number of observations you are trying to print it yes you can print it or else you want to bring all the list of variables in that particular data set you want to go for it yes you can go for it all the data set wise the variables so this is all your do one thing let me run it what is the problem in this all my programs which is there in my powerpoint presentation is ready to execute percentage macro obs nvr and dnm i'm just giving d name is a macro name i'm giving it so sysfunc opening the data set and as well as to find out all the numerical variables in this particular data set we were trying to identify it number of observations we are writing it and as well as number of variables we are trying to identify so let's close the data set once it's open i'm just writing it the data set name i'm writing my data set name sas user library with the name of sas user i'm writing it and class data set let's see what it will give done see it is giving in the sas user library how many observations we have once the data set open we have around 19 observations we have and five variables we have in the data set yes or no yes no got it because this particular functions are working opening the data set once the data set open we are trying to find out how many observations we have and at the same time how many variables we have in the data set and we are printing that message and finally we are closing that particular data set got it so dsid is going to be my data set name so macro variable i'm directly using it here so dsid i'm creating it here i'm using that macro variable wherever it's required for me that's a use of this particular how these particular functions are working and one more is as i have seen str nr str and all the functions met coding functions as i said uh so str function is used yes please so so could you please explain the previous program again first of all percentage macro i'm writing my macro name as a obs n where within that i am creating my macro variable as a d name i'm passing my dna as as user.class now so it will go there as a dname where it will go the dname you are giving it as a you are telling percentage let this id is a new vector variable you are calling it and sysfunc i am trying to open the data set which data set you are trying to open what is this open dot class it will open once it is get opened then we are trying to find out a trend function what a written function will do it will try to find out the number of observations what you have it in the data slash with nobs means number of observations in your data i want to find out into my obs column yes i can get it or else at the same time a train function i'm using i want to know the number of variables in the data set so i'm getting it two variables i'm creating one is obs and nvr is the two macro variable you are creating it i'm printing that message in my log saying it has a number of observations equal to ampersand obs number of variables equal to ampersand and where you're writing it finally as data set you already opened it and you are closing it in the back end so closing my data set so now what happen it will print in my message log how many variables and how many observations in my data setups so yes yeah that's what yes sir and as i explained about the coding function so like a str nr str b code nrb code so we have one more called uh supercode also we can say okay supercode also nr code and all so these are all the different options when that particular options we need to use we have used it macro compiler for a and c what we can do with percentage a and c means a means all these any letters you want to minimize it we can use str and apart from the c so that means with the quotations single quotations are double double quotations if unmatched strings also if you want to use it you can use str function any nr str when you will use it nr str you will use it with abc that means a with with all the values if you want to minimize plus signs comma sorry minus signs star signs flash with co greater than symbols less than symbols any of this special character we can use nrstr also will work on that be caught when you you'll use it nrb code when you will use it super quote when you will use it so these are the descriptions we have it in this particular macro coding functions in detail when you will use str when you will use nrstr when you will use b code when you will use nrb code and you will use super quote and nr code and all i have already mentioned it here i have taken this example from the internet maybe you just go through that and ex try to put a plus sign if i'm putting it into my title is it working or melts if you want to use that particular plus sign suppose you're writing the values as a january uh maybe 23 plus 44 you're writing as a plus sign so if you are using it as a plus sign what happen it will sum your values if you don't want the sum of the values then you can use it str so that the plus sign will be minimized so that things we can able to do with this particular macro coding function this particular slide will helpful so where to minimize and what option to be used you can easily get to know that by using this particular uh option and apart from that the conditional logics the conditional logics is talking about something uh here like a generally in general what we use if you want to use if else condition if if a is equal to 20 then you're writing maybe is group equal to below 20 or 20 20 below or something like that we are writing some condition the same thing we can do with macros also we can write it percentage if the condition then percentage the action else if the percentage condition then action else the action so something like that we used to do it right the same thing we write it says also this is my just best example to explain about it how what we are writing it in says i'm copying it bringing into my sas i'm pasting it here so what i'm just doing it percentage macro x is my macro name what i want to write it i want to writing a false condition percentage if see you no need to write any data step method data run and all no no not required within the macro you can directly write a if else condition within the macro you clearly tell that percentage if sis day that means if my system day equal to friday then do what i want to do proc mean step i want to execute it the piece of code what it is written proc means data equal to self.class class statement section where statement weight and height wise i want to calculate the analysis and run and end if it is a system day equal to friday imagine system day equal to saturday i want to do the proc frequency else do either of these condition he is the system day is not a friday and not a saturday then i am telling his has to execute my proc print step so that my proc print will work now because my system day is not sunday or anything you see that according to me it's a wednesday by default proc print of myself.class it's printing it um one more so now the condition is we have a lot of logics which logic is true which logic is false how i got to know suppose i executed due to some reason i got output the output is generated because of which logic is true right now we have a three logics then you can easily say that the last one is correct but imagine you have around 20 logics which logic is true which logic is false how we get to know that then you will use an option called as we already know that m logic option what am logic will do is come here write it options m logic it will tell that which logic is true which logic is false because which logic you are getting this output also you will get it in your log it clearly says that say which logics are false the first one since they equal to friday logic also false since they equal to saturday logic also falls these two logics are false which one is the last one then only one logic right apart from that when these two conditions are false the last else condition is working if this either of this is not working then finally else do to print proc print so last logic is working that you can get to know that m logic as i said earlier i will explain when condition when coming to the if else conditions i will explain there yes this is the place if you want to know that is that particular if else conditions are properly working to identify so you can use an option called m logic it will tell that is that particular logic is working or not you can get it by using this option right clear how to store the macros yes as of now what you have done is you are writing the macros all the macros will go and default store into your work library you just go there and go to your sas go to your work library default go to work library all the macros what you are storing it you see that check ds is a macro i have written obs nvr is a macro i have written x is a macro have written everything it's just getting stored into this particular work location once you close the sas station open again work library containing information will go off no i don't want it i want to store this particular macros in future also i want to use this for future by default it's creating in work library how i can tell to my sas users has user our location to store the macros or maybe some something else library how i can tell to my sas to store it into that location so i can use it with this particular syntax what you need to write it to storage the macro you write it options m stored equal to sas m store and give a library name you write it this particular line suppose imagine i'm writing it options amp stored equal to sas m store which library you are trying to use it to store it maybe i'm trying to use it as user is a library to store my macros now so i'm writing my macro name as a i can write it the macro i'm writing it so the macro name [Music] you can write it the macro name is for srt macro this is just used for or maybe print macro i'll write it the macro name as a print p it's already there sorry p or i n t huh print me is the my macro name i'm writing it what i'm telling sas to run it so i'm just writing it proc print data equal to i'm writing says help dot class i'm just cl yeah ss i'm using it and run statement i am done yeah so when i'm just executing it what happened the macro is got stored where it's stored now so it should be sas user no so it should be sas user you're writing it that's it now you are storing it i think we are done check your lock perfect go to your sas user is it macro got stored into that location or not i'm not sure first of all i will confirm that so yes we have a location for sas macro print macro is stored now got it so this is the way of storing a macro okay you are storing the macro but if i am writing it percentage if i am writing it directly print will it work yes or no no because if you uh by default take the work library right so that you are saying it won't work it's working will work until right now okay imagine if i close my sas i'll do one thing so the macro is stored into my sas user library i'm just uh closing my sas today perfect now open my sas again [Music] if i'm writing it percentage print will it work it should be there in the sas user then if i'm directly writing percentage printer is it working it's giving a message that the apparent invocation of macro print is not resolved do you know why that why that is not working you created it and you stole it into sas user when i'm writing it now today it's not working how we can retry back the data then you need to write it this option you write it this option libname whatever the library you used it right option sam stored equal to sas m store and give that library location again understand what i'm just doing it options m stored equal to sas m store and give that location that's enough you try writing the same even today also you want to run that macro make sure write it options m stored sas m store equal to sas user you give it is it enough will your print how do you use the nib names yes yes right name statement with options fmt search equal to right we generally use we created the formats and we stored into a different location if you want to use it you need to write an options fmt search equal to you need to mention which library to be located it's the same okay and you stored it into the location you saved it there and if you want to open tomorrow you don't need to run the macro again as it's already stored you just call that particular piece of code is enough to execute it that is one way of doing it and storing the macro agree there is one more way also called percentage include sign let's see what we're going to do with it suppose you have written some macro at desktop at somewhere it's not macro it's a general sas program also fine you stored it at your desktop you want to run that particular program as it is in your sas without any change let's see what i'll do is i'll try to see in my day sas classes there are many files with you there with me i'm thinking that this particular proc format class to be executed so i'll right click on it i'll go to my properties i'll go to security i'll directly write it the copying a path of that particular file where it is located i am just using it click ok then what i will do i can simply say percentage include then quotations you write it the path of that file where it is located and semicolon that's enough you will run this particular line of code is enough automatically it will be executed in your sas see something is wrong there is a question mark in my path you see that there is a question mark on my path i have to remove that question mark what i can do is i'm completely copying it go to my tools go to my program editor i'll place it here there i can see my question mark what i can do is i'll remove that question mark from here and then i'll control a i'll copy the program again and then i'll come to my editor window and then see i can't see that question mark here because it's a non-printable typo we don't know that where it's exactly exist but in log it's saying it's a question mark then i got to know that what to do so what i'll do is i'll go to my program editor and i'll place it there so that is there any non-printable character it will highlight there i'll remove it from there and i'll copy that again from there and i'm pasting it here then it's done now you run this particular program why it will not work we will see see all the program what you have it in that particular sas file how many lines of code you have it in there everything is just got executed with regarding that all the outputs are generated as same can you see that how you are getting it all these outputs did i create it anywhere yes no right so that means it's already there in my program once the program is got executed automatically the program containing outputs will be ready for me i no need to go select the program total and select it and run it no need to do it i can directly say it a command saying it as a percentage include tell my size to just run the what program to run it yes it got executed suppose what happened you you know in real time also you return all the macros and you saved that macros into this particular sas file just imagine you return a program called macros.sas is a program you have written on your desktop so if you want to utilize that particular macros what you will do is percentage include and write that particular macro file where you created you call that particular dot sas file so that all the macros will be executed once it's ready then you can use it for your purpose you don't need to use options i'm stored equal to sas amp store and all you don't need to do that without writing that also we can able to bring it in that way so when you want to use it or else you think that let it be this macros are standard one so every time we will use this macro let it store into that particular sas user or maybe permanent library location then go for another option like earlier what we have already discussed sasam store option this is to execute the program on the spot within that particular program running time only if you want to execute all my macros ready then i want to start my program keep it all the programs in your macros dot sas or somewhere so copy that and put it into include statement run it and then you run your macros what you want it so you call the macros percentage print macro you want to call it percentage how to macro you want to call it you can't call it the macros is already created for you this is the way of using the macros you know a different ways of your software so this is all about for this particular macros what i have yes please wow okay so yeah like uh about the 7bd80 format like that file user like not that says 7bd8 i'm talking about dot sas file only here okay you see that it's a dot sas that means any file any program when you write it where you will save it it will be saved with the name of only right so imagine you have written a big macro so you where you will save that macro maybe you save to desktop what is the file name with that maybe you have written the macro as a my practice macro dot sas right general if you want to write it a name so that if you want to run that macro in size you will directly write it percentage include and then you will write it the path of the macro where it is located okay automatically the macros will be executed first then you will reutilize it you know right how to call the macros now once the macros is created you can you know that right how to call the macro is by using percentage print or something like that which we have already written right now percentage srt and all yes no yeah yes so this is all about your macros and advanced programming what we have discussed we are done with all the advanced programming topics so what is required so thank you all hello everyone today we are going to talk about sql concept sql means it's a structured query language whatever the work we have done with sas the same work we can able to do with sql also but as this sql is not different from our uh regular sql the syntaxes and everything is mostly the same there is no difference in the syntax and all but here we are using it under a proc sql that means it's in a procedure method so in that we are writing this sql concepts each and everything the advantage with this one is mainly in a financial sector they'll use sql in a very uh in-depth manner but in a clinical sector i can say uh it's very less useful because except some places where we use mergings and all we depend on the sql but reminding all those things in a financial sector everything we do with uh um this particular merging sorry uh this particular sqls only so let me talk about this sql so it's almost three days to uh to complete it within sql itself so let me just uh explain about one by one what we can do with sql and writing a simple programs let me just open my software so here go to sas classes day 23 and 24 and go to the sql good so now here what i have is uh data i'm writing it here so sorry it's in a proc means it's in a procedure sql that means it's a structured query long ways you're writing it and quit statement so these are the two things you need to keep uh in mind that every time proc sql and quitter so proc sql means it's in a procedure structured query language and pit means it's like a run statement generally we call it as a right so here we call it as a instead of run we call it as a quitter so now what i'm doing is create a table i'm creating a table that means data your data set name demo how you create it right it's the same way so create a table demo and pid you're writing as a patient id in a number format is in a number format gender in a character format you are writing it here so each and every variable need to be separated with comma where you are coming to in your basis programming of your creating a data set each and every variable just separating with a space is enough but here sql you need to write a comma and apart from that in your basis programming each and every variable no need to mention it's a character or numeric numeric variables defaults as can recognize by default we don't need to worry about it but character values you how to tell something to write it but when you're coming to uh sql programming it's a character and numerical both also should be mentioned clearly if it is in a pi day patient id in a number format and a is in a number format gender in a character format we are writing it and we just done with our declaration part of uh variables here now we will move on to the next concept is called insert into a demo so now you are inserting a values into a demo table the table what you have created you are inserting the values into a demo table the values are 100 that is your patient id that is my first record and 23 is the person of my age and the gender is male another person is one at one with 34 and female another person is one or two with 45 and female that's it you no need to write this particular selector star and all the line you can remove it and run the program what happens you know you are creating a new data set name with the name of demo you will have a three column names like a pid is and gender and three rows also android one at one one or two and 23 and 34 and 45 male female female is the data will be get created into your work library once you execute it and run it the new data set will be get created in your libraries and work library location demo data set it just got created we never know that is it this particular data set is created by using a basis programming rlc sql if you just open it and see it the information which is inside of my data set will be the same there is no difference in that so patient id age and as well as gender wise three variables and three observations containing a data it just got created and this is how output it looks like by default the data city just got created and this is how it's stored now you are interested to store this same information into a different library you can store that also by just mentioning it sas user dot or maybe your own library name anything whatever you want it you can store it into that location also once you execute it and run it the information will be get stored into that location if required go to your sas user library demo data set it just got saved here that means you can give the library name whatever you want it the same rules like our basis rules only we can give your libraries it can be up to eight characters long of your library name and all and apart from that uh the variable names and rules and naming conventions everything as your as you already learned in your basis programming everything is same so there is no much differences in that so this is how we are reading it and execute it and now you can see that the data set it just got created into your work library and now what i want to do is i want data to print this output in output window generally in our basis programming we do have a procedure called proc means procedure print means printing it so that it will print the output in output window by default but i am not getting the output to be displayed in output window how i can print my data set in my output window so very simple you need to write a statement saying it as a select okay you write it select star from that means all the variables i need it from the start from the table name the table which i have created is my demo i'm just selecting it and semicolon so this statement is allowing me to print this particular output in output window and now you can see that output is just generated now you will get a line of one line also at top and apart from that pid is in general the information is just by getting printed by default so this is just a creating of your data set by using sql procedure proc means it's a procedure sql means structured query language creating a table within that declaring a variable then you are just inserting the values into my demo table and the values are looks like this is anyone anyone have any question at any time in this yes i'm sorry go ahead thank you and then if we want to drop one of variables so how we should do that there will be an option for drop and keep also or else if you are not interested i will show you that i can show you that in next programs if you are clear with this particular program that's fine yes yes sir i'm clear thank you yeah i have a question soon yes please uh like the table uh uh like suppose we are storing it in a library we gave sas user dot demo right so similarly when we are printing maybe we can give select star from sas user dot demo you are right but while inserting should we give uh sas user dot demo no so here okay it's a good question we'll try to do that what we do is we'll try not giving it okay so i'm just giving it here as a sas user dot demo i'm writing it and at down also i'm writing it sas user dot demo when i'm inserting it just a demo i have given let's see what happens done it will show that message worked out demo does not exist understand yeah one more wait i think it's a sas user right i have written what is it clear let's see in this case will it give an error or not log is it still showing same work dot demo does not exist why the work. does not exist because you have to tell that the specific location also where to insert it should be sas user dot or demo then only the values will be getting inserted made up okay thank you so very coming to here we'll go for the next program proc sql create a table midi okay i'm writing it the table name and st number store number that means drag in a character format date in a number format s time in a number format e d time in a number format i'm just taking it here different variables store number drug start date and start time end time and all i'm writing it in a number format insert into a midi you are inserting the values into midi the values are store number is 100 the drug is 5 mg the data is your start date of that particular drug is on 12 january of 2003 d d means it's a date constant this is how we can directly insert a values into our data yes time i'm writing it here the time format is 12 o'clock 23 minutes 23 seconds uh in a time format we are reading it here and end time also we are writing it so that means end date and time is this one so end date and time i'm writing it 13th august 2003 13 hours 12 minutes 12 seconds and date and time is the format we are writing it here so this is for one record we are just inserting it you do one thing you try writing it everything is one line so this is for one data set one particular row another row i'm writing it here yes two rows i'm writing it so again one at one with 100 mg and 13th february of 2003 d and as well as time also and date and time combined together select store number and drag you're applying a formats for that what else variables is required for printing it i need a store number and drug also to be printed and apart from that other variables also need it so in that i'm applying a format for that so what format you are doing it here yes date is a format is ddmy by 10 dot yes time i'm writing a format as a time am pm12 dot i'm writing it and date and time i'm writing as a date nine dot for date 18 dot format from midi data set and run it so that what happened you now you execute it and you can see that output with all the information hundred and one at one five mg and 10 mg and the date and the time rls date and time combined together if you want it we just managed with our sql using our dates also with formats also making sense what we have done yes please continue state and s time and the e d time so the dates we have only given as uh numbers in the creation right like we created the table we just said those variables are numbers huh number four we said earlier so we have to specify even though it is a numerical value we have to specify it's a num as a character a format name now means it's a numerical variable no initially we are reading everything as a numerical value that's it that is first thing we are doing it then while giving it we constantly giving a value as a 12th january 2003 d you are giving it that means it's a date constant you are giving it interesting well i mean why i was asking this question is like we give 12 n and 2 0 0 3 right characters are there but still we are only giving this character as we always uh we write it in this way right told the january 2003 quotations with d means it's a date constant sas is automatically written in this particular format as a some number into sas am i right so the number is gonna be around eighteen thousand fourteen hundred fourteen thousand four hundred twenty three days or something like that it will take it right by default when you're writing quotations with d don't consider it is a character yes it will be a character when you are not writing a d there then it is a character okay since if you want to read this as it is in the date format make sure to give it as a date constraint okay yeah sure thank you so thank you now you execute it and run it now you have it yours store number drug s date s time and e date and time you are getting it perfect now we are trying to go for exploring the new program here so how we can able to create a same date uh but this time select star i'm just using it i'm just using the same like earlier create a table as midi and all i think we have did that earlier also you just scroll up so create a table midi and the same thing like earlier but we are writing this particular formats at top okay you can write these formats anywhere that's up to you so serial number uh so here directly what you are doing it so st number that means store number drag in a character format as the date num earlier what happened we written a num and we are applying a formatted down but now what we are doing is we are writing if applying a number and applying a format both also one place you may ask one question i do in this case dual required the num format to be uh created num definitely is required then only this format will be applied to your s data otherwise this format will not be applied so now we are writing it up so here so as date means number format with date nine dot ddm y by ten dot s time will be number so format will be time tolo dot format we are writing it here again end date and time num format we are writing it so the num format we are writing as a date time 18 dot format we are writing it here insert into midi you are inserting the values into the midi so again same thing there is no difference in the above program and this program above program we have written a formatted down while creating it and this one we are writing a format at ebo and apart from that i am writing a select star from self star from midi earlier what happened we are selecting each and every individual variable but now i'm selecting the total variables from my data set that's the only difference but rest all the content what i'm just explaining will be the same like above one inserting the values into my demo table and all those stuff as we have already seen so there is no difference in that okay but the only thing we are writing it select star when you are using a select star that means i am selecting all the columns from this particular data set and executed the same output you will get it you can see that the only is creation-wise it's a different we created earlier one we created the format at down at now you created the format at top except that there is no much difference in your program and apart from that if you want to create a table inserting a data using a set clause what does this mean we already know that by using a set concept we try to copy the information from one data set and pasting into another data set that we already know that so but now we are setting up the values what is required for each and every row wise let's see proc sql create a table lab pid in a num format tester in a character format units in a num format i'm writing it here insert into a lab inserting the values into a lab so pid is 100 test is hr so you are directly giving a value also pid is android the value is we we are directly giving it so earlier what happened insert into a values we used to write it but now there is no values directly we are setting up the values set pid as 100 test as hr and units as a 78 set pid as 100 units as 89 test as dbp and again set pid as 100 test as sbp units as 145 select star from lab i am just executing it and run the program and now you can see that this is how the output is looks like you are setting up the values to create a new data set by using set concept either you can go for value statement also to insert the values into the data set or else you can use the set also but when you are using a setup only the difference is each and every time you are writing the variable name again and again pid equal to test equal to units equal to again pid equal to test equal to units equal to and all but we are coming to the uh earlier one we don't have that issue anymore okay so now we are setting up and this is perfect we are inserting that values directly and this is how it's appearing it's pid 100 test is hr dbp and sbp and 78 and 89 and 145 you are getting it any questions on this particular program yeah so so values is effective than the set statement uh not values are effective i can say that um in this case what happens you know each and every individual variable you are setting up here each and every variable you are typing it here but we are coming to the above one so we can directly insert it it's very simple here so by using a in the basis method by using input in the input statement we try to derive everything whatever is required right in basis programming but we have a one more method called column input method sorry a name input method also you will write every time name equal to that means pid equal to is here we are setting up each and every record it's not data set we are setting to another another data set yeah the observations are set yes yeah so like one question like suppose there is um you know a particular variable doesn't have a value um so particular variable doesn't have a value you take a test variable you don't have any value as a hr you take it as a missing so you do one thing you directly insert it and the value will get inserted as a missing only you see that test variable value for that a particular test is missing yeah that's correct sir but for four values now what will we do like uh we'll just give comma comma ah comma comma that's it you don't need to give anything okay thank you so thank you so much yeah so proc sql so this one is done now we'll go for proc sql select star from uh saselp.class order by age i think we do the same thing in our bases programming also we use a concept called sorting proc sort data equal to your data set name by which variable wise you want to sort it yes i can hear you please continue but can you show the example for missing values so this one you are talking about yes once you have a missing you see test variable value is missing for my first row am i right check that is it got inserted as a missing yeah say i'm asking for missing numeric values ah missing numerical value do one thing pid don't give anything and let's see ah so here the next value here it's showing that we have not assigned anything try to write it as a dot can you see that okay got it character missing value you need to indicate it as a blank and numeric missing value we need to indicate it as a period whenever it's missing right yeah okay thank you sir yeah so we are coming to here so we already know the concept called sorting so if you want to sort the data set yes you can go for it proc sql select start from sasebo dot class order by age each wise if i sorted yes we can do that so now you can run it so the data just got sorted based on the age you want to do the same thing with our basis programming we can do it by using proxor data equal to your dataset name and by which variable wise you want to sort it and run it but here we use a statement called order by okay order by which variable wise you want to sort it you write it that from the data set it will be get sorted and it will be created the output in a sorted manner the advantage with this is i'm taking the data set from the self.class directly it is not overwriting i'm not creating any new data set right it is only for my printing purpose yes it will sort it it will print it but if you are directly working on your proc sort concept prox are data equal to your data set name sas help dot class buy is wise if i select it run it you will get an error message it will show the message user doesn't have appropriate authorization level for this particular action so what will what is the mistake there we have to write a statement called out equal to then you are trying to create that particular sort data set as a new data set and then you can print it in your output if required but sql the advantage is you don't need to create any new data set for printing it automatically sort it and it will print it by default that's a use of order by statement and executing it so that it is just printing the data like this perfect now we'll go for one more is saselp.class order by is but this time it's a descending earlier one default i no need to say anything called ascending or anything so if you want to get these values in a descending order proc sql select star from sasha.class order by is in a descending order so now you can see that the year's wise the values in a descending order you will get it 16 15 14 and 13 12 12 and 11 and all so like a top to bottom so you can see that 16 to 11 you are getting it in this way in a reverse manner in a descending frequency you will get it or else if i'm writing it uh here creating a new table okay you are sorting it and you're printing it output window that's perfectly fine how you can store this particular sorted data set as a new data set you can create a table yes please you're asking question yeah like uh just now i just saw like for in the 60 second line like we have block sequence and then select star from self dot class so there is no semicolon like we're just giving order by and then semicolon so okay so you think that it's the two statements everything is comes under one statement don't limit them process order by a is descending everything comes under until semicolon is one statement yeah okay got it for our convenience you try to write it in the second line otherwise you have to write it in a single line got it good but the 53rd line and all is for printing the lab data table so that is why we just gave select star from lab semicolon ah so this one if you are not interested to print it just let's create the table as a lab leave it you are not interested anymore so that what happened it won't give any errors to you okay it won't give any errors executed it won't give errors you see that but it is not printing an output window that is the only problem lab data as it is created will have the values and everything is ready but it won't print it because you written a separate state procedure so here select star from statement when you are writing it it is exactly equal to proc print statement in your basis programming okay yeah thank you thank you so if i'm writing it order by uh here i'm trying to create a new table as a class 2 from the saselp.class order by a's in a descending frequency so that what happen you are creating a new table as a class 2 as select start from sashab.class order by is in a descending same thing like earlier but this time only the difference is you are trying to create a new table with the name of class 2. now you can see that class 2 data set is sought there with all the descending frequency wise the sorted data it stored making sense yes sir adding as what the it gives as classic select so that means you are creating as a class 2 data set as select you're writing it so as is nothing but so you are just storing that information into your class 2 data set understand if we don't put their answer it wouldn't work right execute it can you see that message yes it's expecting an ass right as is must you how to it's a syntax so create a table class two as selector start from says help dot class order by is in a descending frequency and that's correct okay it's like is it equivalent to the out in proxima ah you're right exactly same proc data set name out equal to new data set name by which variable wise you need to sort it if you want it you will write it decently the difference between your basis programming descending and sql programming descending basis programming by statement after that you will write it descending first then you will write a age right correct but here you will write a first variable name and then you will write it descending this is the exact the difference is the sorting order in a basis programming proc sort data equal to data set name out equal to some xx by descending of years you will write it descending you will write first then followed by your variable name but we are coming to here first you will write the variable name then followed by the descending you will write it yeah okay thank you rock sql select star from sas help dot class where age is greater than 14 can we able to get it so the output will be displayed the people who are the is greater than 14 you can see for greater than or equal to if i understood correct is it correct yeah it's a greater than or equal to so that people who are 14 or above 14 i need all the people information to be displayed in my output window i delete good so now we'll go for proc sql create a table class three select star from self.class i think where is greater than or equal to 14 what is this the earlier one it is just for printing in your output window but i am interested to create a new data set as a class 3 of taking this information from where is greater than 14. yes possible we can do that but i'm doing it right now proc skill created new table as a class three as selective star from sasebo.class where is greater than 14. so the data set of your class 3 will have the values of only the people who are is greater than or equal to 14 people only will be there in your data set this is just for not printing purpose it is just for creating a new data set that's done proc sql select star from self dot class where is greater than or equal to 14 and order by is descending so that what happened rock sql select star from sassaf.class where is greater than or equal to 14 and order by is in a descending execute it and run it now descending frequency wise you will get it so that 16 15 15 14 14 14 and all you will get it in a descending manner you will get it it's the same but order by a is in a descending so you're writing a condition at the same time you are writing in a descending that's the difference earlier one what happened i i did it the sorting different and i just filtered the concept is different but now i'm combining two things together where is greater than 14 is greater than or equal to 14 and order by is in a descending frequency i'm writing it yeah that's working again so come here so this week basis right sorry like we can we can first sort the data and then we can use the where statement in the new data set but we can't do it together in the i don't know in your bases also we can do it no so try to apply the same thing in using bases proc sort data equal to your new data set name that is sas help dot class data set you are writing it out equal to the new data set name as ll semicolon where you can write it where here is greater than r equal to 14 semicolon by age-wise semicolon i'll write it and run suppose if you want it is in a descending you will write in correct you'll do the same yeah it's the same story yellow data set with the descending edgewise the information is there and it's stored possible we can do that same thing with our basis programming also yep now we will move on to the next one proc sql create a table as select pid name from the demo table and quit so that what happen you are assigning a values from the demo table to your jj that means if you want to write it in your basis programming this is the way data jj set demo keep pid and name from the demo table understand you are creating a new data set as a jj and you are copying the values from the demo data set the only pid and name from the demo so the same thing we can do with sql proc sql create a table jj as select pid name from the demo table perfect execute it and run it new data set it just got created with the name offer uh okay first of all is it demo data security i think i don't have any demo let me try writing it as a sas l dot class data set which is there with me i'm trying to create instead of name i'll go for is then there's a name okay execute it and run it yeah i can see it the jj is the new data set a asian name variable is created got it possible the same thing with what you have done with your bases we can do it create a table demo one as select pid as id name from the demo or else maybe i'll change it to tell me i'll go for saselp.class instead of name i will write it as a yes name okay instead of name i'll try to write it as a s name that means student name and a is also required so what i'm just doing is i'm copying the information from the saseb.class but i'm not copying and pasting it while copying itself i'm clearly saying it to rename that particular variable so instead of name i am trying to rename as a sname and age from the sasharp.class and quit and execute it and run it we done go there and go to your new data set of your demo 1 will be created have the values of your name and name is turned into s name because we renamed the variable while copying itself or else the table which is already there with the name of uh demo1 or maybe some table which is already there you are trying to insert some values into the table yes you can directly insert it okay if the demo table is already there you want to insert the values is 14 by 67 something like that you can directly insert it actually the demo table is not ready so if that table is already ready we can directly insert the values you no need to write it a set concept or anything you can directly insert it if the table is already exist with already having some observations we can directly insert the variable we can directly insert the values to that that means we are appending few observations to the existing data set by using a insert concept is it clear any questions yes no shall i move on we could do some change to that right like something that like that says dot class becomes the no we could change it when you are writing a create a table statement or out statement in your basis then you can do some changes to yourself otherwise yeah otherwise it won't work in sql i'm asking sir ah in sql you can print it in your output window with a sorted data you want to print it you can print it but you no need to create a new data set understand yeah that we have already seen yeah right so now if we are adding it but we can't do it with for the help library uh data sets right you can do only to something really different create a table it's possible or else without creating it you print it it's possible but you cannot edit it or modify it in the sas help itself something that's definitely not possible just one request sir can you do insert into demo one and give a just yy and 67 in that uh do one thing demo on what values you have it s name and yes right i'll do one thing i'll directly insert it uh name right first one is name so make sure insert into a demo one okay values quotations i'm writing it okay i'm writing the value as a naidu and i'm inserting the ease of the person is 67 yes is it okay and again i'm writing it one more value i'm inserting into creating a new variable here as john so now i'm just giving my values comma maybe he is a 50 years old just hold on a second 50 years old is it okay from insert into demo one and try to print it as demo one so now you can see two more one more uh or not one more two more observations will be get inserted into the existing table you can see naidu and john is inserted can you see that up to 19 records earlier we have it up to tom up to william but we inserted forcefully two more records yes we can directly insert it making sense clear sorry thank you sir thank you so much that was a mute actually okay thanks that's okay now i'm using my data set as a data employee is your data set name input patient id salary and sale is the variable you are trying to create it a new data set name called employ now perfect now what i'm just doing is proc sql select pid salary plus thousand rupees as a salary sale from employee can you do what happened i'm the mo i'm doing the modifications in the same variable the variable already have a value of two thousand three hundred but i'm adding thousand rupees to that salary i'm storing it in the same variable itself so how i'm doing it proc sql select pid and salary plus thousand as salary that's it that's that simple line is enough to create or else to overwrite the existing variable itself if you're writing it salary plus thousand as a salary so that the same variable will be get overwritten or else if i'm writing it salary plus thousand as a salary x if i'm writing it and sale from the employee table you execute it and run it a new variable will be created over there instead of salary you will have a salary x got it if you think that you need salary also to be side by side you need a salary excel so you need a salary also make sure to copy salary also with comma so i need a salary x i need a salary and sale from the employee table now you can see that the difference between the both now you have a salary x also and you have a salary also so i just added 1000 rupees 2 300 rupees plus thousand rupees i have added 300 as a new salary making sense as a statement here we are using to create a new variable suppose i think ah yes please say i have a doubt in the previous program so in the base size we used to uh write set state in the previous program so ah so here we have inserted the new values in the demo one data set okay so just like in the base as we used to write set statement first see the thing the same work if you want to manage with your basis programming you will try to create as a demo one as a data set and try to create another data set name as a demo 2 then you will set it so that now we can combine the both data sets together agree yes or no i didn't get you sir okay so how you will do it in a basis how you can combine the two data sets together in your basis programming yeah by writing them one and then two in a set statement right writing data your final data set name set demo one space demo two so that now we can combine the two data sets together in your basis programming yes or no the same thing here without writing any set concept we can directly insert the values into the existing table okay right okay so we don't need to write any set statement over here yeah please try to ask on the same point so that no i can explain the area itself thank you um proc sql select share from i think this one is done and now what i'm just doing is proc sql select star salary plus thousand rupees as a new salary from the employee and quit so that what happened has you already done that okay so what you have done you already you already made made it as the salary access the new variables the same thing i am doing it as a different variable name as n salary if required yes you can get it salary sale is there and n salary is a new variable if you want it yeah you can get it that's possible the same thing here proc skill select star from select star i'm writing it here salary plus case okay when sale is greater than 500 then 2 000 rupees else thousand rupees end as a new salary from the employee what is this case this we call it as a case when statements generally if else statement we call it in our basis programming this one is called case when statement what we are doing proc sql select star salary plus case when salary is greater than or equal to 500 then 2000 rupees you are adding to the salary okay as a as a new salary else if that condition is not correct then thousand rupees we are adding it and you are just ending that variable as a and salary so that what happen wherever the value sale is greater than or equal to 500 so there you have 2 000 rupees as added can you see is there any sale 500 i think in this place you see 591 that means sale is greater than 500 so that 2000 rupees we are adding to the salaries so that earlier the salary is 3100 plus 2000 rupees you are adding it the salary is now 5100 you are getting it as a new salary yeah you're getting it and now proc sql select star from salary plus case when sale is greater than or equal to 500 then 2000 when sale is greater than or equal to 400 and the sale is less than 500 that means we are trying to give a bonus to my employees when the sale is happening around greater than or equal to 500 million so so then we are writing it as a 2 000 rupees as a salary two thousand dollars we are giving it as a salary when sale is greater than or equal to 400 and sale is less than 500 that means if the sale is happen within that this range 400 to 500 we are writing as a we are just giving as a bonus as a five sorry fifteen hundred dollars yes thousand dollars we are giving it as a general bonus and end new and salary equal salary from employee execute it and run it now is it done pid salary and sale and salary wherever the scene is yes yes please i mean the concept-wise it is correct it is all okay but i am just ensuring the syntax of the program so it's proc sql and then select star so we are selecting everything from the table yeah and salary instead of giving us that new salary we are giving a case within that whole syntax right uh salary is in salary from employee we were giving like that so within that itself we are giving the case and then completing and then we are telling let's let's say new salary variable will be derived based on your salary plus case okay use n salary variable derivative is based on your salary plus case imagine if sale is greater than 500 salary already there plus 2000 rupees then whatever you are giving it that is your case statement okay n salary equal to salary plus 2000 rl salary plus 500 salary plus thousand as a new salary either of these cases and let it be it may be first case is correct second case is correct or maybe third case is correct it automatically calculate the values as same yeah and they're giving the new variable name after the case yeah that's okay right so now we execute it and now you are done pid salary sale and new salary you are giving it perfect and now proc sql update so if the table is already there i want to update only certain values okay so set we already know that we can directly assign a values to your new variable instead of writing a value statement we have seen earlier we can directly use a set to adding a rows but this case what happened the employee table which is already there but the only problem is the salary variable i want to update it how i want to update it so update employee set salary equal to salary plus thousand rupees let it be let whatever the salary is i'm updating that salary with 2000 rupees extra that means i'm updating a uh what i can say updating existing table okay now we're done we i think gopher it's updated go for employee we have employee thousand rupees few more added now salary plus thousand rupees is added yes now everywhere it's a thousand rupees added you got it salary equal to salary plus thousand rupees if you want it you can add it the same thing i'm just doing it with our basis programming here i'm writing a program saying it as a data or data set name employee input statement pid salary and sale and employee table i'm trying to create it perfect now what i'm just doing is i'm applying a format with a comma format you see now rock sql alter a table of employee add new salary equal to num format the format is comma tallow. you are giving it that means i'm altering the table earlier one what we have done we have updated the table but this time we are altering the table the table is already exist we are altering it how we are altering adding a one more a variable to your existing data set and the one more variable name as a n salary and while declaring itself the salary variable value i'm declaring it in a num format i'm applying the format format as a comma with twelve dot format so that what happened now if i execute it then go there check your new data set alter a table employee table and now you can see a n salary variable value with the formats but why the formats are not appearing is there any problem um um you no need to write it uh i think we write it what is wrong or what is the error you are getting it first alter a table ah you are giving a num format but where you are assigning a value to your n salary as you are assigning any value to the n salary you are creating a new variable value as an salary did you assign any value to the end salary can you see so we are giving it but n salary is how it is so now you can write it as a as statement now i am i think i'm doing it see in this case what happened you know so you see i just assigned the values i'm not assigning any values i ripped any formats and all but still missing because i have not assigned any values now i am assigning values in my next program you see a loading values into my new column update employee set new salary equal to salary plus case when sale is greater than or equal to 500 then 2000 rupees we are adding it when sale is greater than or equal to 400 and sale is less than 500 1500 i am adding it else thousand rupees and quit and now you can see select star from employee and now you can see the new variable with comma separated values earlier one we have given a values it's a missing only we don't assign any values if you want to value if you want to populate that values properly so we have to write a proper values inserted then only you can able to get it that yes please the previous one was it for me sitting the format ah it is just for setting the format and you are leaving it you are not doing any changes to that you are creating a variable and applying a format and making that variable value blank okay in the first one okay a new variable is created with a format that's it if you that is that's what altar will do yes if you think that everything should be in one place then what you do is you write it in the same line itself here you do this then that's enough okay update employee set new salary equal to salary plus case when sale is greater than or equal to 500 then 2000 rupees when sale is greater than or equal to that means you are creating a new variable you applied a format you are assigning a values at the update statement okay okay yeah the same thing i think you asked me right uh mary so how we can drop a variable from a data set suppose imagine rock sql alternate table employee drop salary keep statement you no need to mention in sql keep instead of keep we use a statement called select but instead of drop will be drop even in sql also in your basis programming also you're dropping salary variable from my data i think i did it check my employee table i didn't see salary variable anymore mine my employee can you see that there is no more salary variable only n salary variable is there but there is no salary variable over there yes sir i see yep again this one you see case one statement proc sql select star from uh star case when sbp value is greater than or equal to 170 then 15 mg when sale is when uh sbp value is greater than or equal to uh 50 ah then and sbp value is below that means 150 to 170 within that range do one thing this time most of the times it is getting confused you try writing the program like this so that no it will be easy so when sbp value is 150 above and 170 below then 10 mg of the dose you are giving it else 5 mg and as a drug as a new salary case when again another variable you are creating it sbp value is greater than 170 then 3 and sbp value is 152 170 below then 2 okay i'm writing it here daily and here is a dose two variables you or you just created and now you run the program so that no you will see that's it created select midi i think is it midi is available yes so down next to ellen but here what we asked first of all sbp and all i don't think so those are all values is there with us right where is sbp i think lab data lab also we don't have it spp should be a variable not as observation i think this is this case actually i don't have an example to explain it so but what we are doing is here we are trying to create we are trying to filter the data whoever it is say we are just printing it an output okay we are not creating any data set here we are taking the existing data set of your midi values and we are creating it two more new variables as a daily and dose i think here daily as i think somewhere it's showing daily dose should be with underscore because you should not write a two variable names so daily dose as another variable so one is a drug is a variable another one is daily dose is another variable you are creating it with this particular statement is it clear any questions i will stop here for today and i will continue the remaining concept of tomorrow okay now we will see about the operators so what is this operators operators is nothing but just a joining of two data sets together by using union union all operator and all we'll see i have a data with me so with the name of some date here i'm just writing it and as well as lab one is my data set name and i'm just executing it the new data set it just got created with the name of lab1 there and lab one with values like a so st store number and test and units and as well as you have a date also now what i want to do is in the next program i'm creating one more data set with the name of lab2 here also you have a store number test and units the only difference is here here you have a date but here you don't have any date what i'm just using is i'm just joining the two data sets together by using proc sql unions proc sql select star from lab 1 union all select star from lab2 so that what happen it will join the both data sets together in the two datasets also so you have a store number test and units is the same but the only date is different and now you can see and execute it i'm just joining it can you see that what happened store number test and units and the date will be populated for all the values not only just for one variable so it is just populated for all the values and you can see this is how it is appearing is by default union all means it will just join the two data sets together it won't delete or anything by default it will just join it so instead of union all we can see some duplicate values in your data if i'm understood correct uh can you identify any duplicate values yes here's a 100 out two one or three up so is there any duplicates check your data one or two dbp and sbb one r3 sbp and hr dbp one not i think there is no duplicates in your data no duplicates if yeah so now instead of just using a union operator union all let's see what is the difference if i'm going for just union what is the difference between union all and union if i go for union what it will do is this is your union how many observations you are getting it 35 oh in this data set if i use union how many you are getting it union all how many you are getting it is my question just one that is it combining two of them you just scroll down how many observations here one two three four five six seven eight nine 10 and 11 right 11 observations okay you check it is it the same 1 2 3 4 5 6 7 8 9 10 11 there is no difference what we do is we'll try find out some duplicate values we try enter some duplicate values sbp 178 for one or two right you try give the same here also one or two i'm writing it one or two sbp 178 i will give it here as it is don't change anything now we will see i think the date also will try make it here also 13 instead of 12 make it 13 now we will see the difference what is the difference between union all and union so now i'm just using union all here i'm combining it so you have one or two sbp with 78 178 you are getting it even here also sbp 102 with 178 you are getting it as it is right for sbp so there is no difference but now if i go for union you will not get that second record twice just go for using union instead of union all so it eliminates duplicate value you can see 178 only one time in your data yes you're right it eliminates duplicate values the total record itself it is the same it won't be able to get it in the union all union just having just like a set concept but union all it will remove the duplicate records in your data the total record itself it is a duplicate then union all will be removed making sense yes no yes the difference between unions the union will remove it union will remove it yes but union all will keep records also yeah thanks union all these duplicates union is now duplicates yes you're right you're right perfect so now let's see how to intersect i want to common between the both data set what is the intersect value means this is one data set and this is another data set i want it only this part of information only required okay the common between the both data set the intersect values yes you can get it in this data set you check it data your data set name expo exp you're trying to create in another data set name is unexpected expected and unexpected you are writing it to data sets now i want to know that the common do you have a common observation from the both data sets do you have anything like that yes one of five eyelids yes 105 record is the common between the both data set you want to check that you can check yes you're getting it the common yeah you can get it possible or else except what is accept suppose let me draw the same diagram one more time i'm not at drawing huh just bear with me i'm not good at drawing okay so now i need only this piece of information is required only this piece that means whichever common i don't want it so i don't want this only which are not common only i want it okay only this piece of information how you are going to get it except what except a except b or something like that proc sql select start from exp accept select star from unexpected what is very specific to the unexpected do you have anything specific for unexpected is it no first of all try to understand this one select star from exp accept select to star from unexpected that means i need the values which is there in my exp data set which the observations which should not be present in my unexpected 105 is common between the both data sets so i will not get that but what i need i need value specifically from the exp only which are not present in unexpected i think hundred and one or two records you will get it understand the value is very specific to the exp only you will get it that's it making sense uh able to follow me cynthia yes so yeah recorded me so now if you want it in a reverse manner i need values which is very specific for unexpected but which is not common in my exp so that the values which is very specific for unexpected one or three on one or four you will get it so you can write either of the way that's up to you except with union so now what i'm doing doing is here proc sql select star from exp except select star from unexpected union and then we are writing it the same condition so i'm doing the both also i'm keeping it some variables and just joining also i need it first condition you see first bracket select star from exp except select star from unexpected i need all the values which is very specific for exp only required i'm joining those with people who are with unexpected which are not common in xp so that i wanted only unique values from the both data set so that the 105 record will not be there in any more except that remaining all the data's with us you see that 100 and 102 and 103 and 1.4 103 1.4 from my second data set 10102 from my first data set perfect it is joined together yeah got it except with union all so that what happened it's the same still same i think is there any difference why you will get the duplicate there is no duplicate values in your data understand um yes so no duplicates yeah no dome here means so you will not see the difference between your union and union all right it's the same for both if you have a duplicate then you can see some difference when i'm using union all but it's a normal one so just union only will get it it's the same again union all except it's also same now we will see about joins how we can able to join one data set with another asset here okay simple join and natural join how to join under join and all let's see data exa evident is one data set is there there we have a score number exada and ex date and something like that we have some values and the data set is there with us and it is created exposure with adverse events maybe because of the drug they have given and people are getting some side effects based on that particular dates so now so uh here you can see that they got cold and skin problems are i describe diseases or something like that we got it with the different dates so store number and as well as uh that information is available what they are they have the disease why they got it that that means so that means which belongs to what maybe they have given it's a neuroscience or maybe uh what is that different variable values here but the only difference is only the common is 230 and 156 and 245 is there wherever the values are matching only those values you can able to merge it it's like a merging concept or basis programming how you merge it what we do proc sql select star from x evident with uh unexpected adverse events where exhibited values of my store number is equal to unexpected adverse event of my store number that means it's like a buy statement i'm using it in this case execute it and run only one record that's correct only because the 230 is the common from the both data sets so that you are just getting it now from the both data sets got it again tables with the the same thing i am doing it here instead of giving this data set name again and again unexpected adverse event unexpected adverse event it's a big names instead of that i'm just trying to make it some alias instead of writing my data set name again and again the big name i try to create it as a some small data set so that no we can use it for future reference purpose proc sql select star from x evident as a ex or call it as a unexpected adverse event you are calling it as unux okay so that now i can make it some simple names so that ex dot store number equal to and you annex uh unexpected dot store uh even uh store number is enough so i'm just combining it what is the difference earlier you have a big names of my dataset names but i don't want that dataset names is that much big i try to cut short it instead of just having that big name i'm trying to make it as a ex instead of unexpected adverse event as a separate data set instead of big name i'll try writing as an ex or something like that so we can just make it alias there is nothing special we are just making alias in this is it clear able to follow me yes yes sir yes sir now we are using a inner join what we do is proc sql select a star from x adverse event inner join unexpected adverse event on expected adverse event dot your store number equal to unexpected adverse event what is the change you have done we just used only the inner join earlier we just merged the two data set without using anything you directly mentioned merged it based on that variable now i am using inner join let's see what is the difference can you see is it inner join variable values and as well as the natural general join values are same or anything different it's nothing same thing is it same store number expected ad adverse event and expected adwords event date store number unexpected advertisement unexpected adverse event date so go for this one is it same store number expected and expected investment date and yeah both also absolutely same there is no wi-fi okay either you can go for inner join or you can go for just a joining of two data sets either of the one is same now let's see what i'm just doing is here where expected standard deviation is inner join let's see in this case proc sql select start from expected adverse event as a inner join unexpected adverse event as a uh unix where i'm writing it here so where i'm joining it where ex dot stan ex dot store number equal to unexpected store number only we are writing it i think it will not work if i understood correct yeah you'll get an error because where you should not use it in this way where you can write it with some condition not with where uh you can write it where is equal to or something like that you can write it but you should not write it away in this way definitely it will not work so we should not write a where condition here that is the only thing so so one question sorry yeah please continue like um in line 396 that part of the program we have given inner join like um and just say table one and two table one inner join table two and then on is a new thing that is there right okay so with join should we use on because in union i don't think union not does not required so here in a joints on statement you will use yep oh okay yeah thank you and now you see about right joints proc sql select star from expected adverse event as ex right join as a uh unexpected adverse event it is like exactly if b in your legend concept if n equal to a n equal to b concept right if b what happen all the values which are very specific for b and which are matching from a you will get it c all values which are specific for b and the values which are matching for a you are getting it making sense yes or no concept any questions no no actually we don't remember the data in each table right so maybe if you could uh yeah okay you just see uh in your data set what is the common between the both unexpected adverse event 230 156 and 245 230 is common 230 is common 156 and one for 245 is not common right check it yeah 156 and 245 is not common still it's missing only 230 is common you are getting it all the values which is in right side values you are getting it because you given emerging as a right side right join it's a right side if b concept [Music] so when we gave inner join we got only 230 which was common and which is common that's it oh that means if a and a equal to b you will get it that's the inner joint okay now you are writing it if a only that is your sorry if b only that is right join or else if i'm writing if a that is your left join so that all the left side data set values will be present plus what else right side whatever is matching you will get it you see that all the left side data set values of 230 and 245 and 456 you will get it all the left side values right side whatever is matching you will get it remaining is missing perfect your website data set values will be populated first then whatever is matching from the right side you will get it right join means all the right side data set values populated first then whatever is matching in the left side that you will get it okay thank you i understand full join what is that mean that means there is no unique a or b concept automatically populate all the values can i say wherever you have a value you will have a value otherwise it's missing see in the first record 156 it is there in my second data set but first data set that information is not there it's missing 230 is common in both data sets you are getting it and 345 you are getting it in left side only right side it doesn't have it it's missing 456 it is there in your left side right side it doesn't have it it's missing so i have question what's the difference between union and full join as the union uh gives duplicate values union will join the data sets like a set concept one by another okay what union is joining is just a joining of one data set with another data set that's a union but merging it will arrange the variables in a side-by-side data set one data set to another data set based on the common variable wise it will try to merge it set concept is different and very concept is different that we have already seen on our basis programming right so union is exactly that concept merge is exactly your joining concepts what is like full join right not only full join you can use the left join also left join means n equal to a concept right join means n equal to b concept okay good now natural join means so let's see what happen it automatically gives the values the only it's also same the only difference is you will not see the store number twice earlier what happened store number unexpected unexpected date also an actual advertisement date also expected expert added adwords event date plus your store number used to display twice in your output but now the store number will not be displayed twice it is only once right now you can see earlier we used to have when you are displaying it see store number here and store number here multiple times but in natural join only one time but this is only for printing purposes that's the reason why we can have the same variable twice in your output but if you want to save the data set with the same variable twice in a data set that's not at all possible can we store the data the same variable name twice in your data set in your basis programming also yes or no can we say data your ex name is one variable and again name is another variable can we store a two variable name with the same name possible yes or no no we have to rename it we have to rename it yes you're right the same thing happening here also natural join what it will do is while creating itself it will just remove one of the variable value there that's what happens that's a natural join come here and if you want to write a weird condition proc sql where my bsbp values is greater than my asbp values is there any way in which condition bsbp values is greater than asbp values in which place 198 237 like sd number line number 444 and 445 444 and 445 okay first of all what is that asb value sb value here is 167 178 is here and yes these two two rows you will get it because this one is lesser than that and again i think you can get this one also right 237 one also you will get it so beanie is first one right so 167 greater than 146 huh this is the first value wherever the values which are greater than asbp asbp values is where it is 178 here so 178. this is 167. you will not get this record first record but here 167 but a year 178 yes you will get this record year 156 but here 167 yes you will get this record also year 134 and here 172 yes you will get that yeah you're getting same except first record reminding all the records you'll get it now we'll try to use some aggregate functions like a mean function and all how we can able to use it we will see in sql i'm just creating a new data set name called midi now what i want it proc sql select gid that means i have a gid week 1 and drag 1 and sub week 2 and drag 2 and sub 2 or something like that so gid week 1 and drag 1 and sub 1 only required sum of all my sub 1 as a total i'm creating some of my all the sub 1 variable value i'm summing up the values i'm storing it as a new variable called total and maximum of my sub 1 values i'm taking it i'm trying to create a new variable as a max sub from the midi so how many variables new variables you are creating in this how many new variables you are expecting if i run the program two new variables variable name one is total total right go there you can see total and max sub the total of all the values of my sub 1 wise it's a 166 is it populated everywhere and the maximum value of my sub 1 is 60 right the maximum so it is populated everywhere can i see that it's done or else if you want to calculate an average can we calculate mean maximum and all the value sum of sb1 as the total mean of my sb1 has a average sub maxima of my sb1 has a max up so can we do that what is the difference here we are doing it only the mean we are calculating it extra then earlier one now you can see the mean is this one so that means 56 plus 50 plus 68 divided by 3 it's the 55.333 you'll get it so average also you will get it if you want it so now if you wanted sum of two variables not only just one variable sum of two variables in a horizontal manner let's see um proc sql select some of your sb1 and sb2 as a total from midi so that horizontally you are doing a sum not vertical now you can see horizontally you are doing a sum some of my what is the variables some of my sb1 and sb2 i think sb1 is 56 plus 40 96. yeah correct some of my 50 plus 45 95 sum of my 60 plus missing 61 like getting it it's a horizontal sum earlier one when i'm writing it one variable it's a vertical sum but now this is your horizontal sum if you want it you will get it or else i'm using a group statement group by class clause what is that group by i'm just running the data set okay now i'm writing it what i'm writing in proc sql select star from center trial and sub and sum of your sub as total from clinical group by try so i want to do the sum based on the group wise group wise or trial trial variable wise all the phase one will be grouped at one place all the phase two will be grouped at one place so that you will get the sums accordingly the same now you can see all the phase ones of my group one is 179 is the sum perfect all the phase twos of my uh group is around 679 is the sum perfect right that's a use of a statement called group by and here i am trying to create a new data set name for my next example as a clinical i'm writing it having having center not enough your care okay you are just grouping everything but you are just filtering a condition so where center not enough if the center is the variable is there in your data not enough care is required the values are i need to get the sum of total all the central variable wires but excluding my care information you see now accept care apollo and lim's hospital's information has been populating it right so the same i'm just using a statement called group by but having center not enough your care except care reminding all the people group by some i've required yes you can go for it you can use it count function what is the count so when i'm just using clinical data i'm writing it proc sql count star as obs from the clinical so that what happened you are trying to get the number of observations in this data set as a count variable is a separate variable is required yes you can get it how many observations you have in the data set four observations check it is it four observations or not in your data so first of all open your clinical data set let's see how many observations in your data set four only perfect the four observations is creating as a new data set so that means the count you are getting it how many observations in your data set if you want to get it select count star that means automatically it will count all instead of that only count of center only required how many centers we have apollo excuse me so can you please uh execute that again uh i just missed it actually select count so here you know nim's information is not properly reading actually you do one thing you run it now you see how many observations in your data set here first of all let's keep it and let's see how the data set is that could be how it is read but finally how many observations you have how do you have [Music] [Applause] the count the count of observations for yes or no okay yeah i've got it okay in myself that class how many observations we have do you remember if you want to see i think which says help right 19 perfect yeah you're getting it actually i think there is a lags i'm not able to view the output like properly that's why i asked you to repeat thank you now okay so data clinical input center trial and sub and a sub i'm writing it where center equal to clinical center wise the count how many or what is the count based on the center wise six because you see that center variable wise what is the count now six you are getting it right count off my trial then tell me what happened can we able to get the count of my trial five is it five six seven five why you are not gonna come sir missing will not be considered okay count off ads sub 80 sub what you are expecting four what does it mean it is [Music] four perfect yeah i have a question sir yes please girl it says like count of trial as obs right so as whatever um you know i can even give x right it can be anything like i am giving a new name only right yes it has a new variable name okay so generally you are writing i can even build yes generally you are writing a variable name x equal to as means x equal to you are writing a variable name in your basis programming right the same thing you will do it in your scale okay okay thank you okay i will stop here is any questions okay so now we will go through data clinical is your data set name and here what we do is it is just for group by statement so we are just trying to find out the counts based on the group by earlier what happened we try to find out the counts of everything but we try to find out group by each group wise how many uh values you have it if you want to count it yes you can do it first of all what you do is data your dataset name clinical and all let's try this and then go for proc sql select trial you're giving it a count of your trial as obs from clinical group by trial group by means what happened group first of all trial wise uh you will get it count and what is that count group by count that means all the phase ones are done then you will get it a count of that one let's see each and everything phase one three times in your data phase two three times in your data so you got it so that is your group by statement so we can just get the count also from clinical data if you count it it will give a number of observations count you will get it by default but if you want it a group by each and every category wise count if you want it you can get it this is the same thing we can do with our frequency by using proc freak am i right yes or no in the basis programming right so now we are writing it from clinical group by trial what is the difference in this i think we done the same right so here but we have a missing value here let's see how the missing value will hand over so what happened so you got one more category with missing also zero got it so one more category so you will get it by default the missing value will appear as a zero there by default but in sql what happened in our basis programming what happened missing also frequency one you will get it instead of zero am i right or wrong if you have missing value by using frequency how many missing values in your data the frequency missing count you will get it as a one but in sql you will get the missing value as a zero instead of one oh yeah so so why is it zero sir like it should be showing one ring there is one missing value see here by displaying the uh what i can say is by displaying the category itself it is showing it's a missing value by default missing value it is considering as a zero it will not show it as a one so that's the default our sql but in your base as programming you try writing the same uh do with uh proc sorry i need to use my keyboard just hold on a second proc freak data equal to your data set name what is that clinical clinical huh right table statement based on right t-r-a-i-l right semicolon [Music] run so now you can see you got it right frequency missing equal to one it will directly show it as a it won't show it as a count or anything frequency missing equal to one it will show but in sql what happens you know it will be default displayed as a zero it's a zero yeah um i mean what i am wondering about is so if it is showing zero three and two it is only telling us like there are only five uh trials right that's what categories that means three categories three unique categories you have it in your data one is with missing value another one is with five phase one another one is with phase two yeah okay all the three unique values you will get it count but why you are getting a zero count even though we have a category but why we are getting zero count means so that's a missing value no value okay yeah so come here in this the same thing center group by trial let's see each center counted each trial number of trials okay what happens you know we need it based on the count of center wise by trial i'm giving it center and trial two variables i'm using it for a grouping analysis so now what happened apollo how many times in your data only one time right apollo and phase one is one time and apollo care with phase one is one time if you have any duplicates then you can see that the count is the frequency is true but each and every category is y is right now i'm using a two by variables earlier what happen if i go for only center variable wise instead of trial let's see if i'm writing it only center variable wise so now you can able to see the grouping analysis with the duplicates you can see apollo with phase 1 2 we have it here that means apollos 2 in the data cares are two in your data nims are two in your data that's correct if i go for only center variable wise but i want to get the uniqueness based on your center and trial wise all are unique records if i go for only center there are duplicates if i go for only a trial one there are duplicates if i combine with the center and trial there is no duplicate values in your data that's the reason why the frequency of each when i'm writing it here when i'm writing it to trial also so that you will get it analysis of both yeah you will get it unique values like this and now what we can do is i'm writing it here proc sql select uh center and trial count of center some of your subjects as obs from clinical group by and all so that what happened your count of you're calculating a count of your centers some of your subject subjects is variables which is there and i'm just calculating a sum of those values by uh i'm using center and trial variable wise now what happened two variables wise you will get it one is count and second one is sum you will get it so the count values you are getting it here and some values you are getting it here got it subject variable wise the sum of use unique values wise you are going to get it here good so now you do the same again as total obs you are trying to create this particular uh count as a center of counts and sum of subjects i'm trying to create as a total obs is a new variable i'm creating it from clinical group by center and trial so that you will have a new variable with half of values you see in the total variable the sum you are going to get it what is this 267 refers to where it is getting it from some of the hmm that's the variable sub yes you'll be yes you'll be sum of what apollo yes all the apollo spies we will get the sum and you will get the sum of all the names wise and care wise you will get the sum the values yeah okay so in this category why you are getting without 267 you see a first one what we are getting at the count first of all we try to predict it what value you are getting 267 that means missing value with the sum wise it's a 267 only there is no chance right for them again apollo with fields one is there any other category only one time in your data so that what happened 67 only one time you can see here right i think 67 you have only one time because i'm trying to make it uniqueness i'll try do one thing here in my data itself by only center i will give it don't give a trial just only center wise and now you can see the some values in a different way now you can see total apollo wise how many values we have it apollo in your data you check it you scroll up all the apollo categories by some apollo off 67 here plus 267 total how many 7 7 14 you are getting it last that means 4 is the last value that is your 334 you are getting it the total two opponents also will be populated with the 34 334 again sum of two years and sum of two names wise you will get it when i'm using it some of your counts so why we got two times 334 because each category wise the value will be populated right apollo how many times you have it in your data how many times data two times right so the sum of this particular total apollo will be populated in this particular record also this record also again some of your care will be populated for these two records not for one record yes understand not the apollo apollo you all you won't be able to get some right apollo can we do some no apollo some of their subjects how many number of patients participated in that particular hospital of apollo the number of patients some you are getting it the count as a 334 yes yeah thank you tell me in this particular data i have a data set with the name of clinical okay i'm just creating it now uh proc sql select distinct of your center i'm writing it as a new variable called uh cellist or something that like that from a clinical data so what is the distinct values based on the center wise how many distincts apollo names and care yeah you will get it the distinct values of your apollo nymphs and care no loop key when you use your basis programming right proc sort data equal to your data set by center and node key as a new data set name if you create it this is your new variables right new values apollo wise unique centralized unique and is unique values you will get it good so now you mean to say do pound no do key no do key okay the unique values you want to save it is a new data set name right suppose procs or data equal to a data set name out equal to the new data set name called kk and semicolon by center variable wise so that what happened based on the duplicate so how many times i follow two times first time we'll take it second time will get deleted names two times first time it will take it second time will get deleted care two times first time it will take it second time will get deleted total how many then only three categories right opponents and care only you will get it as output when you are removing after removing your duplicate values in your dataset so the patient is sorry another question just hold on a second let me explain uh the patient around 10 patients is coming to the hospital okay and each 10 patient all the 10 patients coming to the hospital around 10 times so that total how many records per patient for one time one record ten times hundred times but my question is to you how many number of patients participated in the clinical trial the answer is hundred or ten let me let it be the patient is coming to the hospital ten times but how many times you will calculate him only one time your question is please continue yeah thank you sir actually look uh with the distinct function we are just uh checking how many unique values are there or distinct values are there but can we do the count and some in this you do one thing you try writing it try writing it distinct of your sum and apply a count on top of it i'm not getting it let me count off my distinct values trial yes or no i'll get my value as a 3 if i understood correct two because how many distincts i have in my data only two right now check it how many distincts uh i think you removed it here so in this data set oh yeah phase one phase two sir because we are going by trial right right yeah correct phase one and facebook try doing it yeah center variable wise yeah okay okay got it you will get it you will get the count as three yeah so can you keep that count also count program just don't delete keep it in the program please it's already there so you don't don't do this please uh-huh thank you okay i'll just save it and uh yeah you'll get it three right because apollo center and names three categories right you can get it three yeah thank you yeah what is coolest function will do with this one if i go for creating a new data set data clinical and semicolon and run i'm giving it my keyboard when i'm typing it it's not working at some of the case that's the reason why i'm not properly typing it somewhere so let's see clinical data set is there with me now what i wanted here in my next program i'm using a function called coolest if center variable values are missing then i am putting a value as a miss as a center i am writing it here and trial and collage of sub variable missing i want to make it 0 as sub i also need it from the variable so what i'm doing in this total program user first of all how cool is function is working if missing values is finding it then you are assigning to put that value as a n miss for center variable if you find a missing value for subject then you are making it that as a zero and remaining all you are taking it as it is from the data set you see wherever it is missing then you will have a value misser um here yeah like uh last but once yeah you can see center variables are missing so that it will be printed as a miss the last variable here sub variable is missing then you made it as a zero making sense no yes yes yes yes so we are writing a function called collis what qualis will do is it is just using it like this if missing values is there then it is taking it the value from the what we have assigned colleagues works only with missing values or we can do anything missing no listening yeah thank you so now cool sub is there then 0 wherever it says 0 you are just putting it from one data set now one variable we are just changing it see wherever it is a missing you are making it as a zero perfect for sub variable wise and now we will try to play with the views actually what happened you know this views when it's coming to picture you know so we have already seen how to get the uniqueness and how to get the counts and all now we'll try to see the views views in the sensor so you have a data set in your database is a 1 million or 1 trillion records containing a data set you can't copy paste all the data into your work library because you don't have that much of space to do it then you can create a view for from there what you will do is it is just like a temporary data set for you it is just looking into the data it is not for copying the values from here and pasting it no it is just a view table creation so that even though one trillion records containing a data you don't want to all the variables and all the observations from the one trillion data sites one trillion observations are something like that we can keep a few of the variables uh or maybe a few of the observations from the data set that's possible you see i'm trying to create a new data set called clinical imagine this is a big data set you consider it right now i can't create around 1 trillion records right now but you consider this data set as a main data set or master data set what i'm doing is i'm trying to create a view create a view as apollo as select star from a clinical i need where center equal to apollo so that what happened it will not be created as a data set so you just see it's a create view you will get it that means it just got created go there and you can see a data set go to your library work library so now you can see that this data set is bit different from this one because this is the original data set but this is the create view data set you open it and there you can see that the two variables what you are required you see center trial and sub and the variable of only apollo patients information is there actually it is not storing here understand what i am saying this data set is not storing here at all it is picking up the information from my clinical it is showing it is automatically interlinked to my clinical only but it is just for displaying purpose we are using it is it making sense no yes see you can write it instead of create view you can write it create a table also that means what happened you are doing it in that case you are copying and pasting all the data but that is not the intention right i don't have that much of space to do it i'm just creating a view for it write a create view apollo as and then you can continue your job as it is the only difference is earlier we used to write it to create a table but now we write it a creative view but why will we use this uh like we are not reporting imagine in your data say in your database you have connected your sas software to a database and your database have a big table but you don't want all the big table to copy it and paste it into your work library and then you can start doing it you don't have that much of space and that much of time also because when you are copying and pasting a 1 trillion records containing a data set it's a use right so in that case you can create a view and you based on that view you can do your reporting analysis whatever is required most of the cases in real time what we do is we don't want all the variables for our analysis very specific variables are very specific observations is required for our analysis instead of copying all you take only the required one understand okay it's different from pro print right yeah yeah it's just a create view it's a shortcut don't expect it is a separate data set if it is a data set it's appear like this but it's not a data set it's a create view file so can we use this uh view file for your other calculator that's what i'm doing it right now you see so okay you can use it that set also that i am just doing it that also will see so create view if you need it only the information from care so that no you can create a view for care if required see care is created again if you want it only for nimsa as a create view so that no three views we have created one is for apollo patient data you want to look into it click on it here patient's data you want to look and click on it namespace and data you want to click on it you can create a views got it yes now what we do is we'll copy the information from saselp.class and we are trying to create a new dataset as a one you see data your dataset one view one set self.class and where is less than or equal to uh i'm not using any uh sql here you can use it that in the basis also we can create a views now you can see one is the data set created with view option can you see that in your basis also we can create a view if required what is the option you are doing it immediately to your data set name you give a slash and view equal to one so that no we are just creating a view table option guys i can see a message saying it your experience you are experiencing with the drag radio quality as a message let me know if you think that you have some issues with my audio i'll repeat the sentence again one more time okay sir so now if you want to do the same thing uh like a proc skill create a view to as uh this you already know that says help that class you want to make it as a two you can see another view is created for my sasebo.class you can just see that all the dataset values but it's a create view option uh in the same thing we're doing different way right yes i'm just creating one data set with the name alpha employee okay i'm printing it now i want to use the where condition can we use a where condition like this proc sql create a table as a employee one as select star from employee where a cell weighs where sale greater than or equal to 500 can be able to get it as a new data set from here yes yes you can you go there yes check your new data set off your original data set as amp one can you see that wherever the sale is greater than 500 all the values you got it same thing you can see here you can create a data set view also create a view select from employee so you want to take it a new data set from employee to as an employee to as a new data set you want to create it can we do it yes or no we can directly we can create a view for that so now you see emp 2 as a view created you can see that it is from your employee only so that what happened whenever the employee data set is get updated is automatically updated yes or no the create view also will it automatically updates this is all comes under your view and if you want to drop the variables progress skill drop table employee 1 and drop view employee 3 so what happen if you want to drop a tables so i want to delete the employment table i want to delete my employee 3 table i think do you have an employee 3 i think i don't have it i'll remove it if i'm interested to delete the total table itself i can drop a table from employee 1 and execute it see the table one is got deleted amp one table is deleted possible so this is all about for creating a views and so that no we can save our space and time and all we can do that and now this is your proc sql uh i'm connecting to a mdb data set so that we can access the data from your database okay so if the database is already there and you are trying to insert it a new value so let me do one thing let me create a desktop a new folder huh new folder is there i'm trying to create ms office access database go to click on my office access database i created here it's empty one what i will do is i'll directly right click on it rename it i'll make it this database name as a test g s t i'm writing it here okay now i'm writing it proc sql connect to access path equal to you need to mention that where this particular database is located so i'll make sure go to my desktop okay click on view this one you select it right click go to properties go to security access database i'm just mentioning it now i'm just connecting to this path i'm writing it execute i'm creating a new table as a green there i have a value pid in an integer format gender in a character format is in an integer format by access you are giving it here and execute insert into i'm just inserting certain values to my data database the database is already connected i'm directly inserted inserting some values into my database and finally i'm disconnecting from my access maybe i need to see is it will work with access or i do i need to write it access cs also let's see first off i'm just executing it i think uh it's successfully created go there is it you really inserted the values into the database or not you just open it giri is the table created within a gray so you just entered the data of your undead with female and age 45 and again great within the same table one at one with male and 45 you entered it is there anything else is done here only two data you entered into the gui right you created a table with the name of giri you entered pid gender and ya's and all i think it's perfect have you done this clear any questions yes please i mean the question is like we have used integer type right here actually num is the format not available to here you cannot write num so you try to write your integer because when we are dealing with the database it's an integer type only oh okay so yeah is it integer or character that's it that is only two things okay thank you the dollar sign also doesn't work here no no no dollar signs nothing because we are dealing with the database we need to tell that to database what file it is if it is a pid we need to tell that which format it is is it integer value or else is it a decimal values or also if it is just number a date whatever it is you need to mention clearly okay so yeah so what is the execute and by sir the execute statement it is just executing that particular row and inserting into that particular table understand so the database is already created here it's not a ms office excel file it's a database file when we are trying to deal with a database file we trying to tell that execute this particular piece of code to save the file into the ms office access database you are trying to create a new table name with the name of giri there you are writing inserting the pid values gender values and as well as age value pid has integer gender as a character and age is an integer value you are inserting it and you are executing like how we generally call it as a values values right when you are inserting into yeah yeah yeah but instead of that we try to call it as execute finally we are disconnected from the access database so that access database is free and so that we can able to open it now okay okay so let's see so what i'm doing is i'm taking the same example like earlier the path i'm copying it from here insert into okay i'm writing it so two values so that is your 100 and female and 45 you are trying to insert it the existing table is already there and you are trying to insert into the same database yes you execute it it is just connected to the access database i think i have to close my database check my log i think yeah execute could not connect so first of all to uh i mean we gave their guillotine here it is two right so what is the table name here insert into giri you're right madam so earlier we have given as a gray you try writing the same the table is already there no no one more executes sorry what happened second execution step also you have to give insert into giri ah okay so because here check it i think successfully created two more records is inserted check it in your earlier two records now we have inserted one more i think it's inserted right one more record yes or no sorry if you want to create one more table again we have to go again you can go for create a table as a gary or something like that right execute create a table and then you can create a new table with the name of whatever you think you think yeah same thing repeats okay friends okay so that we can do but instead of that insert you need to use create understand see earlier one if you want to correct the earlier program you just keep it as it is too but instead of insert don't do insert create a table understand yes yes yes so write it create a table two and then you can insert the values over there that's possible and you need to declare also it is like how we have done it for gray you do the same thing for down also yeah same thing yeah sir i have a question like is this only for access like can we do excel also like yes yes excel also we can do access also we can do ms office access databases also we can do sql files also we can do any database you can connect it see all those things we are doing it for you can see here we can do the things with excel also understand so it's like similar to import export export concept yes you're right okay okay yeah thank you everything you can see you can see you can connect to your oracle database also but unfortunately i don't have oracle database with my server so that no we can use giving a username and password and you can tell that where that particular server is located and then we can connect it to oracle database also understand okay but unfortunately i don't have the oracle database is installed in my computer so that's the reason why so that yeah if you want it you can do that also thank you thank you yeah sorry go for this yeah mary you can go ahead mary now go for it please yeah thank you thank you sir is this one of the way to connect to a vehicle or is this the only way to connect to arrivals so this is the one of the way another way is you can use the libname statement madam okay okay yeah you can use the name and sql and access database you can connect it directly let's see if you want to connect it access database by instead of using this method you can write it libname okay name libname and then your library name is ccess access and path equal to you write it where that particular access database is located maybe from your desktop you want to connect it you can create a library you can bring it all the information from the access database medium the same thing you can do with sql also we can do with viral also this is another way okay thank you sir why we're learning so many if that's uh one task your job is demanding everything to do in sql okay stick to this one you no need to use your bases programming at all understand what i'm saying no sir yeah or else if you are okay with it you have a leverage in your organization that you can able to use your basis programming also sql is up to you so then you can go either of it okay imagine if the project is already written maybe you are a new person into that particular project the project is already written everything from top to bottom sql they have not used anything bases can we change something to a basis programming there it's be difficult for you to do that right because they are following some standard you have to equip the same thing with the same standard we should not change something over there so you need to stick to it then definitely you need to have a knowledge on the sql you agree with me now yes sir absolutely not understand otherwise it's not my uh intention that so that you i'll make you confuse more yes thank you yeah yeah so the same thing you can connect to your msfs databases and you can uh giving a some of your salary values if you want it you can do the sums also or else if you want to add a new salary value plus a 3000 rupees if you want to do it you can do that this also so i'm just leaving few of the programs for your practice so that it's the same explanation nothing much to be explained over there you just go through it and let me know if you think that if you are not clear suppose if you want to alter the information you can alter it or else you can delete the some of the information from the database you can delete it and you can add it you can modify it anything anything is possible dropping the variables and dropping observations you're giving a conditional statement everything into sql are the same thing you can do with sql what i can say excel also connect to excel path you are giving it execute what you want it create a new table as a sheet 5 or sheet 6 or whatever the sheet name you want to give it and give your store number as an integer format ad type so you are giving it in a character format as a two variables and you are trying to create it let me do one thing for excel also what i can do is i'll try creating it excel on my desktop yeah actually like if we want to input the entire table from sas to the database export sorry export so do we have anything like that like without entering the values each and every time if the my data set is huge if you want to put the entire sas data set to access database exporting sir i'm speaking about exporting actually from sas to access from sas to excel so yes either of these options you can use it madam okay one way another way is you're writing it connecting with path and like a slash with a acc acc db you're writing it right the access database file right maybe that may be just example i'm writing it here ex okay example database what i can do is i can simply say what the what file you want to copy into the database you'll write it proc copy my keyboard okay proc copy in equal to it's from my which database you want to copy uh which data set may be clinical data set first of all i need work library i'm connecting it out equal to i'm connecting what is my new data new library kk so then i can write it select then what do you want it i want to copy only clinical you just write it clinical semicolon and run that's it automatically the total database will be get export the data set will be get exported into your ms office access database method you're copying and you're pasting into that library once you're pasting in that library so that new table will be created with the name of clinical everything will be created there so with proc copy only we can actually do the entire uh lifting entire lifting of the data set individually or else you can use select the star from statement also in this execute select star from statement you can write it instead of this and all you can write select star from uh the self.class or whatever it is then you can export the entire data set and you can insert it there is another way okay yeah you can write a select star from statement i think you can see that so select start from statement we have used it here yeah from connection to access yeah so you can do in that way also if required you can use a multiple ways as we as we already discussed you can directly use a product sorry this connection is a data set connections no not a database so so here start from connections yes it's a database here sorry data set here yeah so could you please explain that method again which one the one which you wrote in the another window ah this one you want it this one don't think that let me connect my access database first of all i'll close it i'm just going to my desktop so my database is here i'll copy the path name access path you are giving it huh you're doing it perfect so let's connect this library first is it connected go there i think kk library connected whatever they are in the grey i think in the data set we are getting it right so now tell me you want to create one more prog copy in equal to work in the work library a lot of data sets which data set you want to send it to the in the database it's a clinical i'm writing it let's uh run this check your log perfect now go to your kk we'll have two datas and two tables one is clinical second one is giri what you will do go to your desktop um you got it clinical data exported from sas to here the total table yes madam yes yes sir yeah this is one way of doing it thank you thank you very much thank you good so now what we can do is i'm just using the same thing with sql whatever we have done with our what is that mdb file the same thing we are doing with sql but the syntax is uh you can write it connect to excel you will write it there you will write it connect to access but now you will write it connect to excel and by excel you will write it here that's the only difference but remaining syntaxes and everything you can use the same which sheet is it is it sheet rc1 sheet2 or c3 whatever the information you can create it so you can export also by using sheets do you think uh do i need to explain the sheets also excel sheets i think it's the same right you expect me to explain the same still i mean can we name the sheet sir like uh yeah yeah that's what we are saying no so you can see uh you are data create a new table as a sheet file got it this is okay thank you thank you so much yeah it is for variable database we are just using it here okay so connect to oracle software which is already installed in your computer you will give the database username and password execute you are creating a new table as a ee in that employee name and all you are creating it and you are inserting you are creating the first column names is it a character format is it a number formatter whatever it is you are trying to create it and then you are inserting the values from to that oracle table but unfortunately i do not have oracle software installed in my computer that's the reason why i can't execute it it's the same steps like how you have learned in your mdb file and access for mdb file excel files so however you have done it right ms office access database files and a text excel file how you have done the same thing is the only extra concept will come into picture as username and password because database is connected with username and password by default that's the reason why we have to stick to that that means the password will be provided by the company itself yes we have nothing to do with the username and password right yeah yeah the password so whatever the access you have it may be they have given only view access that means you can able to see and you can able to copy it that's it you can't be able to edit or modify it maybe your user access level control is up to just to read only access that means you can't modify anything into the database what you can do is you can simply copy the information that's it you can't delete it you can modify the existing values at all based on your level of permission what level you have the permission in the database that actually matters that means which access you have is it a copy access also they have permitted read only is it a copy access permitted then you can copy it or else only they are given access to you not copying also they are not allowing to copy then you can't do anything you can create from there and you can create a view you cannot copy also sir uh just one more question like in this case uh in for oracle execute we when we're defining the variables we're giving number and character so only for access it is integer or no you can give numbers also what is the difference between numbers and integers matter integer means we will not have any decimals right yeah in the field of land right and mathematics whole numbers integers numbers and all integers means what you will not have any decimal value that means you are telling sas in future also we will try to give the values here as an integer if you think that you want some numbers understand so you can say that it's a numbers you can write it uh some floating values you want to give it then you can write it down also the decimal values also how to give it the decimals also maybe some other format is there so we need to check for that and that also you can write it if you think that one point two three point five or something like that you want to insert it i think maybe numbers will help us uh i'm sorry i'm so sorry exactly yeah so like why i asked this uh like in sas we are only using num and care no sir so when integer came here it was new right so that's why i wanted to know is it only for access we are using i int or it i mean it is like even for excel we can use inter mainly with databases you can use the date and all we can use it for databases only this comes into picture not with your excels or anything okay okay thank you so thank you yeah i think what about the non-standard data sir non-standard for information things we are talking about the dates and all yeah yeah yeah so when you are promoting it you should be in a 16th january 2008 d right so it's no more going to understand the data for you okay okay understand when you're moving yes which we have already seen how to give a non-standard data reading into sas software yeah deep constant yes yeah it's a big constant constant yes you're right thank you thank you so today we'll move on to the next concept like a what are the different uh um options we have it in our proxy chart we will see one by one so let me just type sas hey rock g chart we have done h bar and v bar and all so now we'll go for a few more options with our g chart um let me just go with the block chart what this particular block chart will do you know so the same thing like our proc g chart like earlier v bar what we are getting it it's the same thing but the only difference is you will get it in the form of a block like a building block so what you have done here is block g chart or data equal to subject underscore visit block id somewhere equal to subjects you are just giving it here so once you mention it and execute it and run it now you can see that first of all i need to run my graph ready the data should be ready first of all before you run it then so now if i execute it and run it now so now we will get it so you will get it in a graph in the form of a like a a graphical representation like this you will get it by default so now you can see that so the graphs are whatever the values you have it id versus your values so so use id versus so what we have given id versus subjects we have given here so id is appearing on your x-axis and y-axis we are taking it as a uh that your subjects so now the subject value default hundred and one at one will be get summed and this is what you are getting a graph by default so we are not doing anything this so by default this graph will appear in this format so if you are not interested to have this and then what we can do is we can just customize it and you can say that which midpoint so by right now default all the midpoints of your data is just displaying it but if you are not interested to have all the midpoints then you can specify that only january and february only if required in your output if you want it you will get it january wise 878 and february is 300 or something like that you will get it by default and now what we do is in the next pie chart here so we are giving a pie chart means it's in a pictorial representation with a 360 degrees of your data so that means it's a circle format of the data we can able to produce it proxy chart data equal to subject underscore widget i'm just giving it here pi 3d month uh month wise somewhere equal to subjects i'm writing it here execute it and run it now so now you can see that the same graph so it's in a different format now so each and every month wise so with according to your values wise 878 is the highest value so that having the highest portion of your data remaining all according to your data the 360 degree so that means the 100 of the graph will be splitted according to your values wise so 878 means it is occupying the highest percentage that piece is more than followed by your 591 and then followed by the 567 so each and every piece is based on the size of the value so it automatically split it this is called pie chart so that you will get it the output in the form of a circle so it's in a 360 degrees of angle of short you will get it but the same chart you are interested to display it in the form of uh suppose imagine um with the dollar format so you can produce it everything in a dollar format execute it and run it now same thing earlier we used to display some some numbers but now you are displaying with some dollar sign there so there is no much difference in your programming wise we are just applying a format to our values so that it's appearing there and apart from that here we are writing it the option called explode equal to suppose in our some of the business presentation if you carefully observe a piece of information will be get a portrait from that particular uh graph and so that this is the growth or maybe this is a loss for this particular month and something like that what this particular option explode equal to john will do is you just extracting only the january month outside okay so the part of information you are extracting it and you are showing it as separate so that is a use of an option called uh so john 878 or something like that it will be displayed and now we are writing it proxy chart data equal to subject underscore widget pi month somewhere equal to subject's midpoint equal to john in february so now you execute it and run it now you see this is your john 878 and february that means instead of having all the months wise you are interested to have it only specific months yes you can get it uh so the specific month wise also if you want to get it output you will get it and apart from that what we have done as of now hello yeah so i have one question like uh we use the format uh for the subject i mean for the subject variable right so uh you can use it in for any of the block or pi or viva anything anything all the block g charts proc g plots proc sd plots let it be all the procedures so you can apply the format menu okay yeah thank you so yeah so now what i'm just doing is in this particular case so so i am interested to save my data onto my desktop so here i'm interested to save it into my desktop and let me just go to my desktop and i'm just creating a folder on my desktop okay so the folder name keep it as a new folder only i'm not changing anything i'm just copying the path of this particular file what i'll do is in this case right now from now onwards whatever the graph we generated it is only for your output window so now i'm interested to save this particular graph on my desktop okay see user desktop new folder i'm just giving it here so chart one so chart one dot emf i'm giving it as a that is my chart name so that is my file name statement i'm just using it to create that file into that location what are the crop options you are activating it device equal to emf because if you want to support a windows version a window so supported version of the graph you need to use the device equal to mf g unit equal to percentage and rotate equal to landscape f text equal to arial font i want it on a graph i need this title as a count of meetings by the department and apart from that these three options also is no more required what we will do is you no need to write it in a separate i'll try combining this also with our graph options what you have already uh used it here i'll try to paste it here so now you can see that horizontal text is around 13 pixel width and as well as the horizontal title is 15 pixel width gsfm name equal to so graph name you are giving it as a chart one title is count of the meetings by the department you are giving it and that's it you're done so now once you activate it from onwards you run any program all the programs outputs you can able to see in your desktop only you just executed that means this particular path is activated so without having any issue you run any program now suppose you run the pi 3d format of the graph and execute it and now you just select it and run it now you are just giving it see the graph is executed so you can go there and looking into your desktop so there you can see on your new folder huh there is one more time i have to think it will be created now go to your new folder and there you can see the graph is generated you open the graph with any of the default one you can see the graph is saved to your desktop in which format you wanted so it is in a dot image format that is a dot emf is the format you are storing this particular graph to a desktop so that now you can use this image wherever you want to embed it all the options what we are writing rotate equal to landscape the aerial font the title and everything whatever the options you have written in that graph options to save it everything will be just created here and it is there with you okay you can insert this into any of this particular uh tables or maybe any of this um like a pdf document rtf document or somewhere you want to insert it yes you can create it with this and you can copy paste in that destination so that no you can use it even you you can use this into your excel also you want to embed it so you can directly insert as an image so that is possible so these are all the options what we are using it in proc g chart so now we'll try to move on for other concepts what is there in our sas yes please yeah like what is the gsf name is that the name we are giving to the file sir is that the same uh which one so yeah so that is a file name yeah you're right so that is a graph name i can say okay yeah so what okay and give it any name also that's fine uh i mean my question is like uh chart one dot amf is the file name we gave that is a file name i mean so yeah we should keep it same no no you can keep this anywhere okay instead of chart one you can write x y also but whatever the file name you are referencing as a chart one and you are telling that to save this particular data in which form and which name it is if it is your name is here you are writing as an idol here and try writing an idea here this extension containing path you can write it anything it should not be the same okay okay like um like if we are giving a lib name it is similar to that okay i mean but the files name is the chat one dot emf which can be different yeah dot emf you it's mandatory so that you should not change if you want to display that information as a percentages you can display but right now we are not displaying any percentages that's the reason why you will not see the difference okay okay otherwise you can something oh you can see that okay yeah thank you sir so now what we will do is we'll try play with the uh some of the proxy plots as of now i have not activated not explained anything for the g plot let's see all these options how it will work code looks big but don't worry each and every line you'll be able to understand now one by one we'll see what we can do first of all first line we are writing it so g options are reset equal to all so is there any drop options you already activated yes i have already activated some of the graph options it's working on so actually i want to reset all those options you write it reset equal to all so which format you want it see background color equal to white i need a graph background color in a white format so i'm writing it c back equal to white so if you want to have a border then you can have a border also you come here and you scroll down and there you can see so if you want to get this border you can see that the black color one you have a border right so if you want this border you can write it but i am not interested to have my border then i'm writing an option called no border okay so let me write data no here no border each text means horizontal text around 10 pixel width and h title is 12 pixel width i am giving it let me run this particular piece of code is enough to resetting the graph so whatever i have already activated let me reset everything i think we're done go to your log sorry it is just activated without any errors it is just activated now what we will do is i'll try to take this data set as a source what i want to do it in this data set a year so here i can say year quarter wise each and every date wise so how the euro versus pound versus yen the values that currency is changing it okay so euro versus from one one so that means first january of 2008 how the euro value of the pound value how the n value all are starting with zero but we're coming to the april of your first january of april of your january sorry here you can see is it i'm not sure mm right month so month means it's april april 1st of 2008 so the euro value is minus four point two five and zero point three zero some value we have it right now so we try to take this as a data for your reference purpose to explain your graph i'm just executing it done go to uh libraries and work library i think we are done so the data set name called exchange it just got created you want to see that so the data set it just got created ear quarter euro pound and one so year quarter why it's number and it's showing as a number because default the date values you are not applying any format so that it will be displayed as a numbers now i'm giving a title what is the title i'm giving it here the title i'll just write it as a title currency percentage change against u.s dollar you are giving it title 2 you are giving it a quarterly data of your baseline we are giving it what is this titles where it will be displayed you just come here and look into your see the currency percentage change against us dollars is the title and quarterly data of your baseline one one slash one slash two thousand eight and all you are giving it right that is your second title what you are giving it and that that is just for activating your title i just activated my title so i have a two axis one is the x axis and y one is a y axis which access is calling for what we don't know but right now i'm simply declaring it as a axis one as a for my x axis value label equal to none so access one i'm writing it label equal none means so if you just carefully observe in my graph i don't have any label here you see that there is no label on my x-axis and apart from that the values is 2008 to 2013 one one thing i no need to mention it what i'm simply writing is i'm just telling order equal to first january 2008 d2 first january of your 2013 day by year wise so that instead of having each and everything it will be displayed the year so first january 2008 first january 2009 first january 2010 something like that it will be displayed by each and everything on your x-axis wise and apart from that on the same x-axis i'm just giving it a offset equal to one inch to one percentage what is this offset option so if you just carefully observe uh you just get careful observe here so around one percent of gap if you are taking a total x axis is a hundred percentage one percent of the gap it is maintaining in the both sides so that no when we are plotting a values it is not touching your y-axis once you see that after 2008 there will be a so that means the 2008 is not starting exactly where the y-axis is starting it is taking a small gap is the same thing happen here also it is not touching the directly y-axis wall in right side so it is also maintaining some gap why this is gap is here because because of the offset option so this offset option what it will do is it will take how much of gap is required from the both ends you can take it so i'll instead of just having one one i'll try to take it so five percent of the gap i'll maintain it in both side let's see how the graph will appear so this is my one particular label one particular access i just created another access i am doing it here access to label equal to i'm writing it the label is percentage change is the label i am writing it here you see in my graph the percentage change is the label i need it but unfortunately it will be displayed in a horizontal so we are rotating into 90 degrees angle and so that the label will be appear over there and 90 degrees angle we are repeating that sorry rotating that particular label and apart from that minor what is this minor minors and measures are the two things so we need to worry about it always what is minor veins you see that the small tick marks are 0 to 10 there is a small tick mark this is called minor how many minors you have you are expecting from one one particular value to another value on my y axis only one minor is there so and apart from that measures you see that only one major one major means one tick mark one tick mark means one particular value it will apply by default so i am not worried about the majors i am worried about the miners i want to write it 4 minus so instead of just 1 so you can write it so 4 here so so 4 minus each i want it because i'm just trying to change it there is no big deal in this i'm just writing it this particular line of code and i'm just executing it and now done so select it and run the program we are done so now what happened the labels also got activated now what i need to do is simple one symbol two symbol three what is this symbols by default you can't write these particular symbols from your keyboard of your mouse uh excuse me computer yes please yeah okay uh with the offset should it be always same like we should always give it as 1 comma 1 and 5 comma 5 to be equidistant no it is not something like that so 5 comma 0 also you can give it so it means it is in the left side it is taking it up to 5 percent of space right side you are just touching your y axis walls possible matter you can give it okay pi comma five i am maintaining it okay for your reference or maybe for your request what i'll do is okay i'll try make it one five comma one is okay so that means left left hand side of my uh exact y axis y around five five spaces i'm taking it right hand side around one percent of space i'm taking it is it okay for you okay yeah yeah okay so and apart from that the symbols you cannot write it directly from the keyboard so that we need to use some value for this value 80 x is nothing but euro sign and value a3 x is nothing but pound sign value a5 x is nothing but n sine we are using it let me just go for it symbol one interpol equal to join what is interpol equal to join suppose you are just mentioning a graph imagine so you are just generating a graph you're generating a graph and sorry my mouse it's horrible for me it's a new computer i'm not sure so here is one point and here is one point okay so suppose if i'm not connecting any points it will be displayed like that suppose if you are interested a axial line connecting about all these points so that we call it as interval equal to join because if you see in your graph right now what you are displaying it so is all the points is just connected together you see that all the points is connected with lines so that is possible because you are activating it interval equal to join color equal to let's say instead of don't take the color you write it uh this one as a green you write it and the next one here you write it red the next one you write it up in black so you're writing it height the font height how much you want it the font height i need it up to uh 14 pixel height i want it for each so i'm giving it for the three fonts also so that means simple one simple to all the three fonts you are giving it up to 14 and how the symbol should be appear is it in the arial calibrator or times new roman or how you want it you can write it font equal to which one which font you want it and which value it is for euro if you want to write it it's a 80 x if it is a pound it should be a 3 x if it is n it is a 5 x you will write it so you execute it and run it done so now execute this line also done so now we are creating one more line called listen what is listen you come here in your graph so you see that down euro pound and n so how it is appearing at the bottom it is not a label we are not giving any label that is your legend so you can give the values with this but you can create your lessons also if you want it you write an option saying it as a legend legend one equal to repeat equal to one repeat equal to one means let's see repeat equal to three we will write it so that now you will see the difference shape equal to the shape uh height is up to five centimeters height one symbol to another symbol around 2.5 centimeters you are maintaining a gap so you see that in your graph so right now so it's in a five centimeters of your each and every symbol it is taking and around how much 2.5 centimeters of gap you are maintaining it from one symbol to another symbol and apart from that label equal to none i don't want the label i need a frame frame means sorry to interrupt yes please can you please explain that five comma two point four like two point i mean what is that five exactly okay i'll try maintaining this one as a eight point five okay we will see once you execute it and run and you will make this five as a five keep it as it is for a while we'll see what happens label equal to none and frame you are giving it here so frame means you will get that frame by default so now what you are doing is right now executed proxy plot data equal to your data set name exchange plot euro versus found versus n with ear quarter you are giving it so that proc g plot instead of proxy chart or any other procedure we are writing it proc g plot data equal to a data set name exchange plot euro versus bound versus n versus your ear quarter you are writing it so overlay option you are writing it because if you want to draw multiple line on the same graph it's not possible overlay option what it will do is one line on another line you see that around three lines you act you have written it's not possible by default without writing your overlay option so only one line per graph we can write it by default if you wanted a multiple lines on graph on another graph yes you can write it you write an option called overlay and apart from that legend equal to which legend you created and you're calling that h axis that means horizontal axis equal to axis one whatever the axis you created you are calling that a v axis that means vertical axis equal to access to whatever you have given you are calling that so format of year quarter so generally year will be displayed with as a january 1st of now 2000 january 1st of 2008 and february again january 1st of 2009 something like that the total date it will appear i'm not interested by default let's appear only the year is enough so year dot year four dot format is enough for me instead of having the total date so that's it we are done so let me run the program all those things together and let me crea close this particular graph which i have created earlier one now you execute it you can see that what happened what you have done [Music] so you first of all you change the border you remove the border at top so that is first thing you have done second thing is you are maintaining it so the you around five percent gap from the left side left hand side but right hand you maintain one one percent gap i think you you're clear with this why this particular right left side values is taking this much of gap on the right side values why it is not taking much gap making sense yes or no and apart from that we change the color to the red blue and green so sorry red black and green or something like that so we are getting it and apart from that repeat equal to 3 we have given so you see that earlier repeat equal to 1 we used to get it but now i change it to repeat equal to 3 and now you are giving it five percent of the gap from one one particular symbol to another symbol 8.5 you are giving it as a the size of the symbol so now around 8.5 centimeters of your gap so that means height you are just maintaining it because of that the symbols are appearing that much big but you are not interested to have it that much size then you can write it what you want it maybe 2.5 or something like that you can write it label is none so you do the same thing again you can see the graph size is reduced and apart from that euro and pound and n sign is no more having a repetition so only one time it is appearing as a euro and pound and n but remaining all the graph what you are seeing it you are getting it apart from that we said around four miners to get it for each and every uh values wise yes we have a four minus for each so on your y-axis we declared everything does anyone have any question on this yes no like it is um sorry sir i have question regarding the this uh near the zero it didn't start from the beginning because of that five it started a little bit away from the point no you see what this 5.1 percentage gap you know it is just like an offset difference you see that in the left side so where the two naught 2008 is starting why it is taking this much of gap can you see that why it is it happens but if you see in the 2007 you don't have that much of space why it is taking this much of gap here starting off your values itself this this gap because we mentioned around five percent of the gap you try to take it to print the value from the left hand side right hand side only work one percent of gap we are maintaining it so i didn't understand which so we will receive some requirement it's supposed to be like that or how we decided this five point in one point from just manual tuning only it is not that compulsory this much of gap is required there is no thumb rule for that so you can decide how much length you want it if you are not interested to give it or maybe do one thing according to your statistician maybe statistician is not expecting this much of gap from the left hand side then you can suddenly come here and you can do the fine tune it and make it to two percent of gap or something like that maybe this is suitable then you try changing it [Music] okay so thank you yeah so um i mean just a question like if it has to start from zero should we give zero comma zero or how does it go what is the problem okay ah let's see without having a mouse and managing a computer is too horrible for me ah can i see that is it on exactly on y-axis is it plotted yes thank you yeah so zero means there is no no space you are taking it you are just exactly touching your y-ax as well what which one we will receive sir we'll use at least one point one percent gap we try to maintain matter so that no it looks neat so that now it will not touch your y-axis walls one is one ratio we try to maintain as a gap by default okay okay so thank you you're clear so this is all about your proc g plot options what we you have it here you don't need to worry about it so you need no need to write a code from the scratch you come here you take either of the code and you execute it yourself and you will get it understanding of where to change what to change and all and apart from that you just come here and we'll try to see the same graph with a proc sc plot let's see what this sg plot will do same procedure procedure will be the bit different the syntaxes are different so we can able to do it yes please continue yes sir if we are not mentioning any midpoint how it will differentiate actually it's taking some midpoints right huh so the midpoints are by default so the midpoints you mentioned it right here january 1 to january 2008 to january 2013 by ear wise you mentioned the midpoints right you're controlling it that means 2008 2009 2010 10 11 12 and 13 five midpoints you are getting it [Music] default it automatically uh take the range do one thing will not give it anything let's see what sas is giving for us i'm not sure how far this output is correct let's decide the sas is it getting it all the values whatever you have it on your x-axis 2008 to 2013 you are getting it perfect but 2008 multiple times you have it in the data but that is not correct it's not making sense yeah yeah multiple times yeah it's like all the observations are calculated and it's displayed 2008 one value for your hero 2008 one value for your pound and 2008 one value for your n sign something like that you are getting by default how many times you have it and those many times by default you are getting it but if you are not interested then you can write order statement okay okay shall i move okay good so now we'll take this same graph we'll go for this one with proc sd plot let's see what happen in this graph sd plots directly if you see the code is very small here it is explained with ods also let's see that yes now what we will do is we'll try to produce as same so i have a data with me data one so x and y and type is the three variables i have it that's okay i'm interested to create this data set as one fine so the data set it just got created so it's there with me the one is there x and y and type is the variable we have it that's fine now i'm just giving it what i'm giving it proc template i think we have learned somewhere this template concept do you remember yeah so talk table the same thing proc template what we can do is we can define styles equal to symbols for symbols what you want to give it as a styles parent equal to styles dot statistical you are giving it as a um what i can say the parent type apparent you are taking it here style equal to graph data one instead of that you can write excel y also so markers equal to symbol as a circle fields i want it and apart from that contrast color equal to green you are giving it here and again graph data to circle fields you are giving it and red i am giving it that means well earlier what happened so when i'm writing it sub group equal to sex so married by the time she asked how you are going to get the colors by default automatically sas is giving those colors by default but you are not interested with those colors for each and every subgroup wise you want to different color then you can write it so marker contrast color equal to green i'm writing it here contrast calorie called red so that one of the subgroup will appear in the form of green another one will appear in the form of red so now you're writing it the proc template then so now woody's listing close you're writing it that means you are generating this particular graph in the form of html format of the graph so vodius listing close you are writing it [Music] here so ods html file equal to the path of the file where you are interested to save this i'm interested to save it on my desktop so let me just go to the desktop on my new folder i want to save this html document i'll try to copy so i'm just writing it slash highlight.html is the uh so values we are mentioning it here so file equal to path is no more required don't write it style equal to styles dot symbols you are returning uh the style what you declared it and you are calling it and you are writing it block sg plot data equal to one title you are giving it a highlight a value of a graph as the title you are giving it series equal to x axis has a x value and y y axis as a y you are giving it a and scatter plot you are writing it x equal to x and y equal to y and group equal to type that means subgroup analysis you are giving it here ods html close and various listing clause by default it's not giving any print and let's see what happens i think we are running it and we done so now you just go there look into the destination i'm expecting okay what is the log is it showing any error output destination is active okay [Music] what i have done so ods html file equal to you mention on the desktop ods html file equal to and all it should work let's run it one more time i think it's working now is it not saving to your desktop is there any graph options is activated let me just reset all graph options i'm expecting to get this output in your result window you can see huh the graph is generating it here png but why it's not going to save it in html format is it destination is not correct or what i'm giving it folder slash do anything wherever you find it on a html html make it those destination as a pdf um let's see pdf you are getting it so you see that the information what you got it in a pdf so you are getting it but html something is going wrong but why the html is output is not active but the same thing it's working for a pdf you can see here right suppose if you don't want it so you want it a graph in a horizontal manner so or else a landscape manner if you want it then you can write it so in a vodius itself or the style you can write it so orientation equal to you can write it how you want it the graph is so you can write it whatever you want it okay so orientation equal to landscape one question yes please yeah like uh when we say in the graph the y-axis y is like um i mean can you please show the graphs here once uh-huh yes madam yeah yeah the letter y on the left side it is written you know like a vertically so previously we were using the rotate option and also here we don't have such a thing right no you don't need to write it if you want it still you can rotate it you need to define it in your label equal to 90 degrees angle or maybe remaining things you can write it you can customize a lot of steps in the proc template itself matter by default it will appear like that if you think that you want to rotate it or it'll so use a label split option sometimes you want to split the labels into multiple okay do one thing you see i think somewhere we will see progress j okay surrender and it's just showing some options maybe there you will have somewhere the options is label equal to split and rotate and all the options is there by default uh you see this alfred henry and all so it is just appearing it right what they have done you just go there and check it full core i think label overlay progress and can you see something called ah rotate equal to 90 degrees they have written on x-axis can you see that so like that if you want it you can write it for y-axis values also matter okay while declaring an x-axis rotate equal to 90 they have mentioned because of that it's a 90 degrees angle it is rotated at the label so um so how did you find they have written for like x axis where is it mentioned like x space ah okay so here they mentioned it here right it's an x-axis only you can see oh okay understand this is all about your values what you are getting it here this is your x equal to you're writing it here yeah thank you so much right so like this any graphs we can able to produce 18 pdf for rtf document or any styles you can generate it by using this particular proc template so this is all about different graphs so you can use any graph or you can able to produce any graph right right now in your real time okay if you are asking me also so try to generate a graph from scratch i may not be able to do it because so nobody is going to learn the these many lines of code to just buy hot data or something like that we'll take some reference code and we'll try to use our knowledge to modify here and there and we will survive that's what how we do matter thank you yeah you can try any of these graphs any of these graphs you can write and then it will work for you so this is all about for today so what i have planned to deliver [Music] you