Select name, age, height, weight, weight/height as bmi from sasuser.admit order by 5 /* the sorting would be done by 5 i.e bmi in asc order*/ Select name, age, height from sasuser.admit order by 1 /* the sorting would be done by 1 i.e name in asc order*/ Number of the vars can be used to sort the table Q10 Can the sorting be done by column position?Īns: Yes the table can be sorted by the column position, the select statement list the vars and the order Select * from sasuser.admit as a inner join sasuser.acities as b on a.name=b.name Select * from sasuser.admit as a,sasuser.acities as b where a.name=b.name Say name having the same values you can use the where condition as well as inner join keyword and on condition, it always gives the values from table A that are common with table B Q9 What is an inner join,and whats the use?Īns: The inner join is the intersection of two datasets or the tables,if you want to find the values in two tables for a common variable Select * from sasuser.admit a ,sasuser.acities b where a.name=b.name Select * from sasuser.admit as a ,sasuser.acities as b where a.name=b.name Q8 How can you join using the table aliases? show by eg.?Īns:The tables or the datasets can be joined using the table aliases or the other name,in all the joins and by the where condition The relevance in sql join is that if you have a common variable and matching values then the value of each row from first datatset gets repeated for values of second dataset. Q7 What is cartesian product and its relevance in the sql joins?Īns:The cartesian product is a concept in the sql, where the observations of the first dataset is repeated every time for each obs of second dataset Īlso note that in cartesian products AxB ^= BxA Select name,monotonic() as order from sasuser.admit Remerges the stats with the table, the stats is calculated for the whole group, and they tend to summarize or collapse the whole table Ģa.count(*) /* Gives total count missing+nonmissing*/Ģb.count(var) /* Gives the non missing count*/Īns:If you want to serially count the values in sql, use the monotonic function Q4 What is an aggregate or summary function? Name the ones which can be used with sql?Īns: The aggregate or summary function calculates the stats for that particular variable and Like Excel,Access,oracle sql, my sql,BO,hyperion,sybase, MSSQLserver 20 In fact when you use proc import, it makes a sql connection for importing the data. The sql has the power to process the table column wise as compared to a SAS datastep that does it row wise, helpful in determining sum column wise.ĥ.It can connect to other DBMS, while the data step cannot. The second dataset do not overwrites the value of the first one for a common var.Ĥ. You can merge two datasets even if the key var names are diff, without renaming the key vars.ģb. While merging two tables you do not need to sort in sql as contrary to the datastep.ģa. The result comes by default to output window no need of proc print statement.ģ. The programmer need to write a few steps as compared to a data step.Ģ. Q3 What are the advantages of using sql over datastep?Īns:The follwing are the advantages of using sql over the datastep.ġ. Q2 What are the different ways of creating a view?Ĭreate view b as select * from sasuser.admit ***Here the view called in the datasetp always fetches the updated data from sasuser.admit, so even if u run the datastep, you would get the updated data from sasuser.admit** Ĭreate view a as select id, age from sasuser.admit OR simply use in the program, just like any other dataset.Ĭreate table a as select * from sashelp.vcolumn To execute a view you need to just double click.
The views are used to save the space on the drive as well as to restrict the access also the biggest advantage is that the view always give you the refreshed data, in a library a view and dataset of same name can not exist. Retrieves data from the existing tables when called in a datastep.
Ans: SQL View: A sql view is not a dataset it is basically a stored query or select statement that
0 kommentar(er)
