Stacking Up - Horizontal Or Vertical With PROC.pptx [Read .
Stacking Up ‐ Horizontal or Verticalwith PROC SQL or DATA StepWisconsin Illinois SAS Users GroupMilwaukee28 June 2017Charu ShankarTechnical Training SpecialistSAS Institute Canada Inc.Copy r ig ht S AS Institute Inc. All r ights reser ved .
AgendaVertical Stacking Data & Proc Steps PROC SQL Set Operators Compare and contrast?2. Horizontal Stacking Data Step Merge PROC SQL Joins Compare and contrast?3. Questions4. Useful Links5. Contact1.Stacking up Horizontal or Vertical with PROC SQL Or Data Step, Charu Shankar, SAS,WIILSU28 2017Jun 201728 ,Jun2
About your instructorCharu Shankar trains and develops curriculum in SAS data access,analysis, reporting and big data solutions.For over 20 years Charu has delivered computer languagetraining to SAS customers globally and at the United Nations fieldoffice in New Delhi.Skilled in customer needs analysis, Charu recommends the rightSAS training to customers.Work blog y yoga & cooking www.charuyoga.com28 Jun2017Stacking up Horizontal or Vertical with PROC SQL Or Data Step, Charu Shankar, SAS,WIILSU,28 Jun20173
Wide or long? Broad or narrow?How do you want to go?VisualStackingPROC SQLData StepHorizontalStackingstack columnsand align rows.JoinsMergeVertical stackingrows and DatasetsStacking up Horizontal or Vertical with PROC SQL Or Data Step, Charu Shankar, SAS,WIILSU28 2017Jun 201728 ,Jun4
Now some languageData StepPROC SQLVerticalStackingConcatenateData Step ConcatenateProc AppendHorizontalStackingMergeMatch MergeNon MatchesSet OperationsUnionOuter UnionExceptIntersectJoinsInner JoinOuter JoinLeft JoinRight JoinFull JoinStacking up Horizontal or Vertical with PROC SQL Or Data Step, Charu Shankar, SAS,WIILSU28 2017Jun 201728 ,Jun5
1. Vertical StackingStacking up Horizontal or Vertical with PROC SQL Or Data Step, Charu Shankar, SAS,WIILSU28 2017Jun 201728 ,Jun6
1. Vertical Stacking ‐ Proc Append ConceptsStacking up Horizontal or Vertical with PROC SQL Or Data Step, Charu Shankar, SAS,WIILSU28 2017Jun 201728 ,Jun7
1. Vertical Stacking ‐ Proc Append SyntaxStacking up Horizontal or Vertical with PROC SQL Or Data Step, Charu Shankar, SAS,WIILSU28 2017Jun 201728 ,Jun8
1. Vertical Stacking ‐ Proc Append OutputStacking up Horizontal or Vertical with PROC SQL Or Data Step, Charu Shankar, SAS,WIILSU28 2017Jun 201728 ,Jun9
1. Vertical Stacking ‐ Proc Datasets SyntaxPROC DATASETS LIBRARY libref;APPEND BASE SAS‐data‐set DATA SAS‐data‐set;RUN;Proc datasets library work;Append base emps data emps2008;Quit;Stacking up Horizontal or Vertical with PROC SQL Or Data Step, Charu Shankar, WIILSU28 2017Jun 2017SAS, 28 ,Jun10
1. Vertical Stacking ‐ Proc Datasets OutputStacking up Horizontal or Vertical with PROC SQL Or Data Step, Charu Shankar, WIILSU28 2017Jun 2017SAS, 28 ,Jun11
1. Vertical Stacking ‐ Proc Append /ProcDatasets unique casesStacking up Horizontal or Vertical with PROC SQL Or Data Step, Charu Shankar, WIILSU28 2017Jun 2017SAS, 28 ,Jun12
1. Vertical Stacking ‐ Proc Append Tips Ensure that both datasets have the same exact variablesand variable attributes to avoid a ‘messy’ log (i.e. avoidusing the FORCE option if at all possible). Explicitly define the dataset name using the ‘DATA ’option in the PROC APPEND statement. If ‘DATA xxxx’ isnot specified, SAS uses the dataset most recentlycreated. Use Proc Append with WORK datasets. Avoid using withpermanent datasets. Designate the larger of the two datasets as the BASEdataset to improve processing efficiencySAS, 28 Jun2017Stacking up Horizontal or Vertical with PROC SQL Or Data Step, Charu Shankar, WIILSU,28 Jun201713
1. Vertical StackingProc Append vs. Proc DatasetsWHATProc AppendProc DatasetsRUN group processingNoSupports Run‐group processing‐tosubmit multiple run groups withinthe same procedure without endingthe procedure. Ends with a quit.Default libraryWork or UserNoneData ManagementNoYesData BuildingNo for both. Use FORCE option if the data dataset has a differentstructure from the Base datasetProcessing timeBehind the scenes same code is used so little performance gainshould be expected.Stacking up Horizontal or Vertical with PROC SQL Or Data Step, Charu Shankar, WIILSU28 2017Jun 2017SAS, 28 ,Jun14
1. Vertical Stacking ‐ Data Step ConceptsConcatenate like‐structured data sets empsdk and empsfr to create a newdata set named enmarkDenmarkDenmarkempsfrFirstPierreSophieGender anceFranceBoth data sets contain the same variables.SAS, 28 Jun2017Stacking up Horizontal or Vertical with PROC SQL Or Data Step, Charu Shankar, WIILSU,28 Jun201715
1. Vertical Stacking ‐ Data Step SyntaxempsdkFirstLarsKariJonasExecutionGender F SophieGender CountryMFranceFFrancedata empsall1;set empsdk empsfr;empsall1run;PDVFirstSophieGender MFMMFCountryDenmarkDenmarkDenmarkFranceFrance28 Jun2017Stacking up Horizontal or Vertical with PROC SQL Or Data Step, Charu Shankar, SAS,WIILSU,28 Jun201716
1. Vertical Stacking ‐ Data Step OutputViewing the LogPartial SAS Log145146147data empsall1;set empsdk empsfr;run;NOTE: There were 3 observations read from the data setWORK.EMPSDK.NOTE: There were 2 observations read from the data setWORK.EMPSFR.NOTE: The data set WORK.EMPSALL1 has 5 observations and 3variables.SAS, 28 Jun2017Stacking up Horizontal or Vertical with PROC SQL Or Data Step, Charu Shankar, WIILSU,28 Jun201717
1. Vertical Stacking ‐ Data Step AdvantageScenario 1Standard data stepconcatenateScenario 2.Build new variablesScenario 3Determine which table contributedto final resultsCreate new table byreading every row inevery table listed on theset statementBuild new variables in thenew datasetThe data step can keep alluncommon columns, plusdetermine which table contributedto final results.data ta dataconc;setwiilsu.prepsaleswiilsu.nonsales;sales ‘YS’;nonsales ‘YN’;run;data dataconc;setwiilsu.prepsales(in ins)wiilsu.nonsales(in inn);sales ins;nonsales inn;run;SAS, 28 Jun2017Stacking up Horizontal or Vertical with PROC SQL Or Data Step, Charu Shankar, WIILSU,28 Jun201718
1. Vertical StackingProc Append/Proc Datasets vs Data stepACTIONPROC APPENDData Step ConcatenateHanding of differentvariables in datasetscannot include variables foundonly in the DATA datasetUses all variables and assignsmissing values as necessaryNumber of data sets2Any numberSpeedProc Append much faster sinceit doesn’t process observationsfrom BASE data setSlower‐ has to read every data setlisted on SET statementBuild new variablesor Create new TableCannot build new variables asdescriptor portion informationin base SAS dataset cannotchangealmost always the best method forcreating a table from several inputdatasets or building new variablesin the same data stepSpaceProc Append only reads inobservations from datasetbeing appended. Use ProcAppend over the SETstatement to save work space.SET statement reads in allobservations from the datasetsbeing concatenated.Stacking up Horizontal or Vertical with PROC SQL Or Data Step, Charu Shankar, WIILSU28 2017Jun 2017SAS, 28 ,Jun19
1. Vertical Stacking ‐ PROC SQL ConceptsSet OperatorPROC SQL provides traditional set operators from relational algebra:OUTER UNIONconcatenates the query results. Similar to data step concatenateUNIONproduces all unique rows from both queries.EXCEPTproduces rows that are part of the first query only.INTERSECTproduces rows that are common to both query results.SAS, 28 Jun2017Stacking up Horizontal or Vertical with PROC SQL Or Data Step, Charu Shankar, WIILSU,28 Jun201720
1. Vertical Stacking ‐ PROC SQL ConceptsSet OperatorData is stored in 2 tables.Partial train 012Which employeeshave completedtraining A or B?Training class A is completed in asingle session. Date represents thedate of training.Partial train 12.Training class B is a multi‐session class. SDate isrecorded on the first trainingday. EDate is recorded whenthe course is complete.SAS, 28 Jun2017Stacking up Horizontal or Vertical with PROC SQL Or Data Step, Charu Shankar, WIILSU,28 Jun201721
1. Vertical Stacking ‐ PROC SQL ConceptsSet OperatorSet operators use the intermediate result sets from two queries to create afinal result set.Query 1:Intermediateresult set 1RS1?Final ResultSetSetOperator Query 2:Intermediateresult set 2RS2SAS, 28 Jun2017Stacking up Horizontal or Vertical with PROC SQL Or Data Step, Charu Shankar, WIILSU,28 Jun201722
1. Vertical Stacking ‐ PROC SQL SyntaxSet Operator general formSELECT UNION OUTER UNION EXCEPT INTERSECT ALL CORR SELECT ;The modifiers ALL and CORR change the default behaviorof the set operators. ALL modifies the default behavior for rows. CORR modifies the default behavior for columns.28 Jun2017Stacking up Horizontal or Vertical with PROC SQL Or Data Step, Charu Shankar, SAS,WIILSU,28 Jun201723
1. Vertical Stacking ‐ PROC SQL ConceptsOuter Union Set OperatorWhich employeeshave completedtraining A and/or Band on what dates?Query 1:List employeesthat have completedtrain a and thecompletion date.OUTERUNIONQuery 2:List employeesthat have completedtrain b and thecompletion date.Final ResultSetRS1RS2SAS, 28 Jun2017Stacking up Horizontal or Vertical with PROC SQL Or Data Step, Charu Shankar, WIILSU,28 Jun201724
1. Vertical Stacking ‐ PROC SQL SyntaxOuter Union Set Operatorkeep all rows and allcolumns from the twointermediate resultsets with The OUTERUNION operatorproc sql;select * from train aouter unionselect * from train bwhere EDate is notmissing;quit;SELECT OUTER UNION CORR SELECT RS1RS2s106d04SAS, 28 Jun2017Stacking up Horizontal or Vertical with PROC SQL Or Data Step, Charu Shankar, WIILSU,28 Jun201725
1. Vertical Stacking ‐ PROC SQL OutputOuter Union Set OperatorWho completed Training A and /or B and on what dates?CompletionStartID NameDate NameIDDateEnd ƒƒƒƒƒƒƒƒƒƒƒƒƒ11 Bob15JUN2012.16 Sam05JUN2012.14 Pete21JUN2012.21 Chris07JUN2012.18 Kim04JUN2012.17 Pat22JUN2012.20 Mary11JUN2012.12 Sue06JUN2012.87 Ted05JUN2012.91 Rand07JUN2012. Bob11 09JUL2012 13JUL2012. Pam15 25JUL2012 27JUL2012. Kyle19 12JUL2012 20JUL2012. Ted87 09JUL2012 13JUL2012SAS, 28 Jun2017Stacking up Horizontal or Vertical with PROC SQL Or Data Step, Charu Shankar, WIILSU,28 Jun201726
1. Vertical Stacking ‐ PROC SQL ConceptsUnion Set OperatorQuery 1:List employeesthat have completedtrain a.Final ResultSetRS1Which employeeshave completedtraining A or B?UNIONRS2Query 2:List employeesthat have completedtrain b.SAS, 28 Jun2017Stacking up Horizontal or Vertical with PROC SQL Or Data Step, Charu Shankar, WIILSU,28 Jun201727
1. Vertical Stacking ‐ PROC SQL SyntaxUnion Set OperatorThe UNION operator in Proc SQL is used to append rows of two or more SELECTstatements having the same number of columns with similar data types.proc sql;select ID, Name fromSELECT work.train aUNIONunion ALL CORR select ID, Name from SELECT ;work.train bwhere EDate is not missing;quit;s106d01SAS, 28 Jun2017Stacking up Horizontal or Vertical with PROC SQL Or Data Step, Charu Shankar, WIILSU,28 Jun201728
1. Vertical Stacking ‐ PROC SQL OutputUnion Set OperationWhich Employees Have CompletedTraining A or B?ID Nameƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ11 Bob12 Sue14 Pete15 Pam16 Sam17 Pat18 Kim19 Kyle20 Mary21 Chris87 Ted91 RandSAS, 28 Jun2017Stacking up Horizontal or Vertical with PROC SQL Or Data Step, Charu Shankar, WIILSU,28 Jun201729
1. Vertical Stacking ‐ PROC SQL ConceptsExcept Set OperatorWhich employeeshave completedtraining A, but nottraining B?Query 1:List employeesthat have completedtrain a.Final ResultSetRS1EXCEPTRS2Query 2:List employeesthat have completedtrain b.SAS, 28 Jun2017Stacking up Horizontal or Vertical with PROC SQL Or Data Step, Charu Shankar, WIILSU,28 Jun201730
Vertical Stacking ‐ PROC SQL SyntaxExcept Set OperatorList employees who have completed training A, but not training B with theEXCEPT operator.proc sql;select ID, Name from train aexceptselect ID, Name from train bwhere Edate is not missing;quit;SELECT EXCEPT ALL CORR SELECT s106d07SAS, 28 Jun2017Stacking up Horizontal or Vertical with PROC SQL Or Data Step, Charu Shankar, WIILSU,28 Jun201731
1. Vertical Stacking ‐ PROC SQL OutputExcept Set OperatorWhich Employees Have CompletedTraining A, But Not Training BID Nameƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ12 Sue14 Pete16 Sam17 Pat18 Kim20 Mary21 Chris91 RandSAS, 28 Jun2017Stacking up Horizontal or Vertical with PROC SQL Or Data Step, Charu Shankar, WIILSU,28 Jun201732
1. Vertical Stacking ‐ PROC SQL ConceptsIntersect Set OperatorWhich employeeshave completedboth training Aand B?Query 1:List employeesthat have completedtrain a.Final ResultSetRS1INTERSECTRS2Query 2:List employeesthat have completedtrain b.SAS, 28 Jun2017Stacking up Horizontal or Vertical with PROC SQL Or Data Step, Charu Shankar, WIILSU,28 Jun201733
1. Vertical Stacking ‐ PROC SQL SyntaxIntersect Set OperatorRows that exist in both train a and train b. The INTERSECT operatorwill accomplish this.RS1RS2proc sql;select ID, Name from train aintersectselect ID, Name from train bwhere EDate is notmissing;SELECT quit;INTERSECT ALL CORR SELECT s106d09SAS, 28 Jun2017Stacking up Horizontal or Vertical with PROC SQL Or Data Step, Charu Shankar, WIILSU,28 Jun201734
1. Vertical Stacking ‐ PROC SQL OutputExcept Set OperatorEmployees Who Have CompletedBoth Training ClassesID Nameƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ11 Bob87 TedSAS, 28 Jun2017Stacking up Horizontal or Vertical with PROC SQL Or Data Step, Charu Shankar, WIILSU,28 Jun201735
1. Vertical Stacking ‐ Proc Sql or Data step?ComparisonProc SQLLengthLess lengthy codeSpeedMay execute faster for smaller tablesPortabilityMore portable for Non‐SASprogrammers & Non‐SAS ApplicationsData StepTransparencyData step explicitly printsmessages in logManipulationData step arrays, hash objectsSortingNo explicit code required for sortingSame namedvariablesNot required, although same typeand length are required.CautionBuilds Cartesian products if JOINcriteria not explicitly listed.Reading nonDBMS dataCannot readData step strength. Ability toread variety of input datasources including non‐RDBMSSAS, 28 Jun2017Stacking up Horizontal or Vertical with PROC SQL Or Data Step, Charu Shankar, WIILSU,28 Jun201736
2. Horizontal Stacking28 Jun2017Stacking up Horizontal or Vertical with PROC SQL Or Data Step, Charu Shankar, SAS,WIILSU,28 Jun201737
2. Horizontal Stacking ‐ Data step MergeA B C123C D E123A B C12C D E112A B C124C D E234ConceptsMatch‐Merging28 Jun2017Stacking up Horizontal or Vertical with PROC SQL Or Data Step, Charu Shankar, SAS,WIILSU,28 Jun201738
2. Horizontal Stacking ‐ Data step ConceptsOne‐to‐one MergeMerge the Australian employee data set with a phone data set to obtaineach employee’s home phone number, storing the results in a new dataset.empsauphonehFirstGender EmpID EmpIDPhoneempsauhempsauhFirstGenderEmpID PhoneSAS, 28 Jun2017Stacking up Horizontal or Vertical with PROC SQL Or Data Step, Charu Shankar, WIILSU,28 Jun201739
2. Horizontal Stacking ‐ Data step SyntaxOne‐to‐one Mergedata empsauh;merge empsau phoneh;by EmpID;run;MERGE SAS-data-set1 SAS-data-set2 . . .;BY DESCENDING BY-variable(s);The data sets are sorted by EmpID.Stacking up Horizontal or Vertical with PROC SQL Or Data Step, Charu Shankar, WIILSU28 2017Jun 2017SAS, 28 ,Jun40
2. Horizontal Stacking ‐ Data step OutputOne‐to‐one 150 61(2)5555-1793121151 61(2)5555-1849121152 61(2)5555-1665Gender rinGenderMFMEmpID121150121151121152Phone 61(2)5555-1793 61(2)5555-1849 61(2)5555-1665SAS, 28 Jun2017Stacking up Horizontal or Vertical with PROC SQL Or Data Step, Charu Shankar, WIILSU,28 Jun201741
2. Horizontal Stacking ‐ Data StepMatches & Non MatchesIN Data Set OptionThe IN data set option creates a variable that indicates whetherthe data set contributed to building the current observation.MERGE SAS-data-set (IN variable) variable is a temporary numeric variable that hastwo possible values:0Indicates that the data set did not contribute to thecurrent observation.1Indicates that the data set did contribute to the currentobservation.SAS, 28 Jun2017Stacking up Horizontal or Vertical with PROC SQL Or Data Step, Charu Shankar, WIILSU,28 Jun201742
2. Horizontal Stacking ‐ Data Step MergeMatches & Non MatchesempsauFirstTogarKylieBirinphonecGender 0 61(2)5555-1795121152 61(2)5555-1667121153 61(2)5555-1348data empsauc;merge empsau(in Emps)phonec(in Cell);by EmpID;run;matchp110d08PDVFirstTogarGender EmpIDM121150DEmpsPhone1 61(2)5555-1795D28 Jun2017Stacking up Horizontal or Vertical with PROC SQL Or Data Step, Charu Shankar, SAS,WIILSU,28 Jun2017Cell143.
2. Horizontal Stacking ‐ Data Step MergeIsolating Matches & Non MatchesempsauFirstTogarKylieBirinGender EmpIDM121150F121151M121152EmpIDPhone121150 61(2)5555-1795121152 61(2)5555-1667121153 61(2)5555-1348data empsauc;merge empsau(in Emps)phonec(in Cell);by EmpID;run;PDVFirstKylieExecutionphonecGender EmpIDF121151DEmps1non‐matchPhoneD28 Jun2017Stacking up Horizontal or Vertical with PROC SQL Or Data Step, Charu Shankar, SAS,WIILSU,28 Jun2017Cell044.
2. Horizontal Stacking ‐ Data Step MergeIsolating Matches & Non MatchesempsauFirstTogarKylieBirinphonecGender EmpIDM121150F121151M121152EmpIDPhone121150 61(2)5555-1795121152 61(2)5555-1667121153 61(2)5555-1348data empsauc;merge empsau(in Emps)phonec(in Cell);by EmpID;run;PDVFirstBirinGender EmpIDM121152ExecutionDmatchEmpsPhone1 61(2)5555-1667D28 Jun2017Stacking up Horizontal or Vertical with PROC SQL Or Data Step, Charu Shankar, SAS,WIILSU,28 Jun2017Cell145.
2. Horizontal Stacking ‐ Data Step MergeMatches & Non MatchesPDVFirstTogarKylieBirinGender EmpIDM121150F121151M121152121153DEmpsPhone1 61(2)5555-179511 61(2)5555-16670 61(2)5555-1348DCell1011The variables created with the IN data set option are only available duringDATA step execution.They are not written to the SAS data set. Their value can be tested using conditional logic. 28 Jun2017Stacking up Horizontal or Vertical with PROC SQL Or Data Step, Charu Shankar, SAS,WIILSU,28 Jun201746
2. Horizontal Stacking ‐ Data Step MergeIsolating Matches onlyAdd a subsetting IF statement to select the employees that have companyphones.data empsauc;merge empsau(in Emps)phonec(in Cell);by EmpID;if Emps 1 and Cell 1;run;empsaucFirstTogarBirinGender EmpIDPhoneM121150 61(2)5555-1795M121152 61(2)5555-1667p110d08SAS, 28 Jun2017Stacking up Horizontal or Vertical with PROC SQL Or Data Step, Charu Shankar, WIILSU,28 Jun201747
2. Horizontal Stacking ‐ Data Step MergeIsolating Non Matchesfrom empsau onlySelect employees that do not have company phones.data empsauc;merge empsau(in Emps)phonec(in Cell);by EmpID;if Emps 1 and Cell 0;run;empsaucFirstKylieGender EmpIDF121151Phonep110d08SAS, 28 Jun2017Stacking up Horizontal or Vertical with PROC SQL Or Data Step, Charu Shankar, WIILSU,28 Jun201748
2. Horizontal Stacking ‐ Data Step MergeIsolating Non Matchesfrom phoneC onlySelect phones associated with an invalid employee ID.data empsauc;merge empsau(in Emps)phonec(in Cell);by EmpID;if Emps 0 and Cell 1;run;empsaucFirstGender EmpIDPhone121153 61(2)5555-1348SAS, 28 Jun2017Stacking up Horizontal or Vertical with PROC SQL Or Data Step, Charu Shankar, WIILSU,28 Jun2017p110d0849
2. Horizontal Stacking ‐ Data Step MergeIsolating all Non‐matchesdata empsauc;merge empsau(in Emps)phonec(in Cell);by EmpID;if Emps 0 or Cell 0;run;empsaucFirstKylieGender EmpIDPhoneF121151121153 61(2)5555-1348Use the OR operator, not the AND operator.p110d0828 Jun2017Stacking up Horizontal or Vertical with PROC SQL Or Data Step, Charu Shankar, SAS,WIILSU,28 Jun201750
2. Horizontal Stacking ‐ Proc SQL JoinsTypes of JoinsPROC SQL supports two types of joins: Inner joins return only matching rows. Outer joins return all matching rows, plus nonmatching rows from one orboth tables.LeftFullRightSAS, 28 Jun2017Stacking up Horizontal or Vertical with PROC SQL Or Data Step, Charu Shankar, WIILSU,28 Jun201751
2. Horizontal Stacking ‐ Proc SQL JoinsCartesian ProductA query that lists multiple tables in the FROM clause without a WHEREclause produces all possible combinations of rows from all tables. Thisresult is called a Cartesian product.proc sql;select *from customers, transactions;quit;SELECT FROM table‐name, table‐name , ,table‐name ;To understand how SQL processes a join, it is helpful to understand theconcept of the Cartesian product.s104d01SAS, 28 Jun2017Stacking up Horizontal or Vertical with PROC SQL Or Data Step, Charu Shankar, WIILSU,28 Jun201752
Horizontal Stacking ‐ Proc SQL JoinsBuilding Cartesian t 100 52 212Result SetID NameID ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ101 Smith 102 Purchase 100101 Smith 103 Return 52101 Smith 105 Return 212104 Jones 102 Purchase 100104 Jones 103 Return 52The Cartesian product104 Jones 105 Return 212is rarely the desiredresultofa102 Blank 102 Purchase 100query.102 Blank 103 Return 52102 Blank 105 Return 212Non‐matchingIDs9 rows28 Jun2017Stacking up Horizontal or Vertical with PROC SQL Or Data Step, Charu Shankar, SAS,WIILSU,28 Jun201753
Horizontal Stacking ‐ Proc SQL JoinsCartesian product featuresThe number of rows in a Cartesian product is the product of the number ofrows in the contributing tables. 3 x 3 91,000 x 1,000 1,000,000100,000 x 100,000 10,000,000,000Partial SAS LogNOTE: The execution of this query involves performing one or moreCartesian product joins that cannot be optimized.SAS, 28 Jun2017Stacking up Horizontal or Vertical with PROC SQL Or Data Step, Charu Shankar, WIILSU,28 Jun201754
2. Horizontal Stacking ‐ Proc SQL JoinsInner Joinreport showing all valid order information.customerstransactionsID ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ102 BlankPurchase 100SAS, 28 Jun2017Stacking up Horizontal or Vertical with PROC SQL Or Data Step, Charu Shankar, WIILSU,28 Jun201755
Horizontal Stacking – Proc SQL Inner JoinSyntaxSpecify the matching criteria in the WHERE clause.proc sql;select *from customers, transactionswhere customers.ID transactions.ID;quit;PROC SQL OutputSELECT object‐item , object‐item FROM table‐name, table‐nameWHERE join condition AND sql‐expression other clauses ;ID NameID ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ102 Blank 102 Purchase 100SAS, 28 Jun2017Stacking up Horizontal or Vertical with PROC SQL Or Data Step, Charu Shankar, WIILSU,28 Jun2017s104d0256
2. Horizontal Stacking ‐ Proc SQL Inner Joinvs DataStep MergeA PROC SQL inner join and the DATA step match merge can return the same results.proc sql;select c.ID, Name, Action,Amountfrom customers as c,transactions as twhere c.ID t.ID;quit;s104d04data orders;merge customers(in c)transactions(in t);by ID;if c 1 and t 1;run;proc print data orders;run;28 Jun2017Stacking up Horizontal or Vertical with PROC SQL Or Data Step, Charu Shankar, SAS,WIILSU,28 Jun201757
2. Horizontal Stacking – Proc SQL Inner Joinvs DataStep Mergecustomer2A PROC SQL inner join and theDATA step match merge will notalways return the same rchasePurchaseAmount 376 119 57 98select *from customer2 as c2, transaction2 as t2where c2.ID ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ102 Kent102 Purchase 376102 Kent102 Purchase 376102 Kent102 Return 119102 Kent102 Return 11928 Jun2017Stacking up Horizontal or Vertical with PROC SQL Or Data Step, Charu Shankar, SAS,WIILSU,28 Jun2017s104d0558
2. Horizontal Stacking – Proc SQL Inner Joinvs DataStep turnPurchasePurchaseAmount 376 119 57 98data work.new;merge customer2(in InCust)transaction2(in InTrans);by ID;if InCust 1 and InTrans 1;run;proc print data seReturnSAS, 28 Jun2017Stacking up Horizontal or Vertical with PROC SQL Or Data Step, Charu Shankar, WIILSU,28 Jun2017Amount 376 11959
2. Horizontal Stacking ‐ Proc SQL Outer JoinConceptsYou can retrieve both non‐matching and matching rows using an outer join.Outer joins include left, full, and right outer joins. Many tables can bereferenced in outer joins. The tables are processed two tables at a time.LeftFullRightSAS, 28 Jun2017Stacking up Horizontal or Vertical with PROC SQL Or Data Step, Charu Shankar, WIILSU,28 Jun201760
2. Horizontal Stacking ‐ Proc SQL Outer JoinConceptsall customers and any recent transactions that they have completed.customerstransactionsID NameID ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ101 Smith.102 Blank 102 Purchase 100104 Jones.SAS, 28 Jun2017Stacking up Horizontal or Vertical with PROC SQL Or Data Step, Charu Shankar, WIILSU,28 Jun201761
2. Horizontal Stacking ‐ Proc SQL Outer JoinSyntaxOuter join syntax is similar to the alternate inner join syntax.proc sql;title 'All Customers';select *from customers as cleft jointransactions as ton c.ID t.ID;quit;The ON clause specifiesjoin criteria in outerjoins.s104d07SELECT object-item , object-item FROM table-name AS alias LEFT RIGHT FULL JOINtable-name AS alias ON join‐condition(s) other clauses ;SAS, 28 Jun2017Stacking up Horizontal or Vertical with PROC SQL Or Data Step, Charu Shankar, WIILSU,28 Jun201762
2. Horizontal Stacking ‐ Proc SQL Outer JoinOutputPROC SQL OutputAll CustomersID NameID ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ101 Smith.102 Blank 102 Purchase 100104 Jones.SAS, 28 Jun2017Stacking up Horizontal or Vertical with PROC SQL Or Data Step, Charu Shankar, WIILSU,28 Jun201763
2. Horizontal Stacking ‐ Proc SQL Left Amount 100 52 212select *from customers c left join transactions ton c.ID t.ID;ID NameID ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ101 Smith.102 Blank 102 Purchase 100104 Jones.Includes all rows from the lefttable, even if there are nomatching rows in the right table.28 Jun2017Stacking up Horizontal or Vertical with PROC SQL Or Data Step, Charu Shankar, SAS,WIILSU,28 Jun2017s104d0864
2. Horizontal Stacking ‐ Proc SQL Right Amount 100 52 212select *from customers c right join transactions ton c.ID t.ID;ID NameID ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ102 Blank 102 Purchase 100.103 Return 52.105 Return 212Includes all rows from the righttable, even if there are nomatching rows in the left table.s104d0928 Jun2017Stacking up Horizontal or Vertical with PROC SQL Or Data Step, Charu Shankar, SAS,WIILSU,28 Jun201765
2. Horizontal Stacking ‐ Proc SQL Full Amount 100 52 212select *from customers c full join transactions ton c.ID t.ID;ID NameID ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ101 Smith.102 Blank 102 Purchase 100.103 Return 52104 Jones.105 Return 212Includes all rows from bothtables, even if there are nomatching rows in eithertable28 Jun2017Stacking up Horizontal or Vertical with PROC SQL Or Data Step, Charu Shankar, SAS,WIILSU,28 Jun2017s104d1066
2. Horizontal Stacking ‐ Proc SQL Self JoinConceptsname of all sales employees and the name of each employee’s directmanager.orion.employee addressesorion.employee organizationEmployee ID Employee NameManager IDManager �ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ120140 Minas, Michael120103Dawes, Wilson AU120141 Liebman, Amanda120103Dawes, Wilson AU120142 Eastley, Vincent120103Dawes, Wilson AU120143 Sloey, Phu120103Dawes, Wilson AU120144 Barbis, Viney120103Dawes, Wilson AUSAS, 28 Jun2017Stacking up Horizontal or Vertical with PROC SQL Or Data Step, Charu Shankar, WIILSU,28 Jun201767
2. Horizontal Stacking ‐ Proc SQL Self JoinConceptsTo return the employee name and the manager name, you need to readthe addresses table twice.1.Return the employee’s ID and name.addressesEMP ID EMP NAME100 John101 SueorganizationEMP ID MGR ID10010110157EMP ID EMP Name MGR ID MGR Name100 JohnSAS, 28 Jun2017Stacking up Horizontal or Vertical with PROC SQL Or Data Step, Charu Shankar, WIILSU,28 Jun201768.
2. Horizontal Stacking ‐ Proc SQL Self JoinConceptsTo return the employee name and the manager name, you need to readthe addresses table twice.1.2.Return the employee’s ID and name.Determine the ID of the employee’s manager.addressesEMP ID EMP NAME100 John101 SueorganizationEMP ID MGR ID10010110157EMP ID EMP Name MGR ID MGR Name100 John101SAS, 28 Jun2017Stacking up Horizontal or Vertical with PROC SQL Or Data Step, Charu Shankar, WIILSU,28 Jun201769.
2. Horizontal Stacking ‐ Proc SQL Self JoinConceptsTo return the employee name and the manager name, you need to readthe addresses table twice.1.2.3.Return the employee’s ID and name.Determine the ID of the employee’s manager.Return the manager’s name.addressesEMP ID EMP NAME100 John101 SueorganizationEMP ID MGR ID10010110157EMP ID EMP Name MGR ID MGR Name100 John101 SueSAS, 28 Jun2017Stacking up Horizontal or Vertical with PROC SQL Or Data Step, Charu
Left Join Right Join Full Join . Vertical Stacking ‐PROC SQL Syntax Outer Union Set Operator. Stacking up Horizontal or Vertical with PROC SQL Or Data Step, Charu Shankar, SAS, 28 Jun 2017 26Charu Shankar, WIILSU, 28 Jun 2017 1. Vertical Stacking ‐PROC SQL Output Outer Union Set Operator Who completed Training A and /or B and on what .
The 1960 switches support stacking through local and cloud-managed stacking modes. Local stacking provides True Stacking to easily configure, manage and troubleshoot physical switches as a single entity, while cloud-managed stacking provides ease-of-setup via the Instant On mobile app. Using either the Instant On mobile app or the cloud-based
O-D SPECIAL TEAMS COACHES MANUAL 2015 Stacking System The Stacking System is a teaching method where core offensive and defensive concepts are used to keep Special Teams SIMPLE (NOT NEW learning – stacking of the BEST). *This is modeled and researched from Auburn University – HC Gus Malzhan The University of Oregon – HC Mark Helfrich The University of Michigan – HC Fielding
The Maddox Rod Test. Maddox Rod (vertical streak with horizontal rods). Patient estimates horizontal separation between light spot and vertical streak . Patient estimates vertical separation between light spot & horizontal streak. Patient fixates the right eye red horizontal streak &
Horizontal, Vertical, Parallel, Perpendicular Notes 1 December 18, 2018 Dec 16 1:24 PM Horizontal and Vertical Lines Dec 16 1:25 PM Horizontal Line a line that goes straight from left to right, parallel to the x axis of the coordinate plane (all of the points on the line will have the same y coordinates)
Vertical 0.01 feet (per KDOT Construction Manual, subsection 3.09 - Finishing Stakes, Part III). For Horizontal, use a GPS system or a Total Station. For Vertical, use a Level or Total Stations. Do not use GPS for Vertical. Critical Bridge Member Staking: Horizontal 0.02 feet; Vertical 0.01 feet (Vertical as per
Multi-position, Upflow, Horizontal Left, or Horizontal Right, Modular Air Handlers Black Epoxy Coil GAF2A0A18S11EE GAF2A0A24S21EE GAF2A0A30S21EE GAF2A0A36S31EE. 2 Pub. No. 22-1858-10 Features and Benefits . Horizontal Left Horizontal Right 1 1 Upflow . Trane .
heat transfer, free drop deformation, puncture, stacking loads, etc. Fire retardant polyurethane foam is installed between the two layers. A Viton-rubber seal is installed between the upper and lower shells for leak tightness. Four (4) stacking pads are attached, one to each corner of the upper shell to allow stacking. Four (4)
Description Logic Reasoning Research Challenges Reasoning with Expressive Description Logics – p. 2/40. Talk Outline Introduction to Description Logics The Semantic Web: Killer App for (DL) Reasoning? Web Ontology Languages DAML OIL Language Reasoning with DAML OIL OilEd Demo Description Logic Reasoning Research Challenges Reasoning with Expressive Description Logics – p. 2/40. Talk .
