proc sql number; /*1*/ select empdata.empID, /*2*/ empdata.LastName, empdata.Salary, newsals.NewSalary, (newsals.NewSalary-empdata.Salary) /*3*/ as Raise format=dollar10.2 from certadv.empdata inner join /*4*/ certadv.newsals on empdata.EmpID= newsals.EmpID /*5*/ where calculated Raise>3000 /*6*/ order by EmpID; /*7*/ quit;
1 | The SQL procedure retrieves data from tables or views to generate a report. The PROC SQL option NUMBER prints the row numbers in the query output. |
2 | The SELECT statement retrieves the columns EmpID, LastName, and Salary from Certadv.Empdata and NewSalary from Certadv.Newsals and displays the column values in the query result. |
3 | The SELECT statement also creates a column alias by using the AS keyword followed by the column name of Raise. The value of Raise is the difference between the column NewSalary from Certadv.Newsals and Salary from Certadv.Empdata. The DOLLAR 10.2 format is applied to the new column, Raise. |
4 | The FROM clause uses an INNER JOIN keyword to join Certadv.Empdata and Certadv.Newsals. |
5 | The ON clause specifies the column, EmpID, to be used when joining Certadv.Empdata and Certadv.Newsals. |
6 | The calculated keyword tells PROC SQL that the value is calculated within the query. The WHERE clause subsets the data to include only the observations where the value of Raise is greater than 3000. |
7 | The ORDER BY clause sorts the rows by EmpID. |
3.147.73.147