SQL Sample Coding

《SQL Sample Coding》由会员上传分享，免费在线阅读，更多相关内容在学术论文-天天文库。

1、Chapter1:BasicSQlProgramingSyntaxLIBNAMElibref‘SAS-data-library’;PROCSQL;CREATETABLEtable-nameASSELECTcolumn-1<,...column-n>FROMtable-1

2、view-1<,...table-n

3、view-n>>>;QUIT;SampleProgramsQueryingaTableprocsql;selectempid,jobc

4、ode,salary,salary*.06asbonusfromsasuser.payrollmasterwheresalary<32000orderbyjobcode;quit;SummarizingGroupsofDataprocsql;selectmembertype,sum(milestraveled)asTotalMilesfromsasuser.frequentflyersgroupbymembertype;quit;CreatingaTablefromtheResultsofaQueryonTwoTablesprocsql;createtablework.milesassele

5、ctsalcomps.empid,lastname,newsals.salary.newsalaryfromsasuser.salcomps.sasuser.newsalswheresalcomps.empid=newsals.empidorderby2;quit;Chapter2:AdvancedQueriesSyntaxPROCSQLOUTOBS=n;SELECTcolumn-1<,...column-n>FROMtable-1

6、view-1<,...table-n

7、view-n>>

8、Gexpression>1>;QUIT;BETWEENvalue-1ANDvalue-2sql-expressionCONTAINSsql-expressioncolumnIN(constant-1<,...constant-n>)columnISMISSINGcolumnISNULLcolumnLIKE‘pattern’sql-expression=*sql-expressionSampleProgramsDisplayingallColumnsinOutputandanExpandedColumnListintheSASLogp

9、rocsqlfeedback;select*fromsasuser.staffchanges;quit;EliminatingDuplicateRowsfromOutputprocsql;selectdistinctflightnumber,destinationfromsasuser.internationalflightsorderby1;quit;SubsettingRowsbyUsingCalculatedValuesprocsqloutobs=10;validateselectflightnumber,datelabel="FlightDate",destination,board

10、ed+transferred+nonrevenueasTotalfromsasuser.marchflightswherecalculatedtotalbetween100and150;quit;SubsettingDatabyUsingaNoncorrelatedSubqueryprocsqlnoexec;selectjobcode,avg(salary)asAvgSalaryformat=dollar11.2fromsasuser.payrollmastergroupbyjobcodehavingavg(salary)>(selectavg(salary)fromsasuser.payr

11、ollmaster);quit;SubsettingDatabyUsingaCorrelatedSubqueryprocsql;title’FrequentFlyersWhoAreNotEmployees’;selectcount(*)asCount2fromsasuser.frequentflyerswherenotexists(select*fromsasuser.staffmasterwherename