1 / 79

Chapter 4: Creating List Reports

Chapter 4: Creating List Reports. Objectives: Specify SAS data sets to print Select variables and observations to print Sort data by values of one or more variables Specify column totals for numeric variables Add titles and footnotes to output Assign variable labels

alcina
Download Presentation

Chapter 4: Creating List Reports

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 4: Creating List Reports • Objectives: • Specify SAS data sets to print • Select variables and observations to print • Sort data by values of one or more variables • Specify column totals for numeric variables • Add titles and footnotes to output • Assign variable labels • Apply formats to values of variables

  2. Overview of PROC PRINT List reports are typically generated with the PRINT procedure. The SAS System Emp Job Obs ID LastName FirstName Code Salary 1 0031 GOLDENBERG DESIREE PILOT 50221.62 2 0040 WILLIAMS ARLENE M. FLTAT 23666.12 3 0071 PERRY ROBERT A. FLTAT 21957.71 4 0082 MCGWIER-WATTS CHRISTINA PILOT 96387.39 5 0091 SCOTT HARVEY F. FLTAT 32278.40 6 0106 THACKER DAVID S. FLTAT 24161.14 7 0355 BELL THOMAS B. PILOT 59803.16 8 0366 GLENN MARTHA S. PILOT 120202.38

  3. Overview of PROC PRINT You can display • titles and footnotes • descriptive column headings • formatted data values. Salary Report Emp Job Annual Obs ID LastName FirstName Code Salary 1 0031 GOLDENBERG DESIREE PILOT $50,221.62 2 0040 WILLIAMS ARLENE M. FLTAT $23,666.12 3 0071 PERRY ROBERT A. FLTAT $21,957.71 4 0082 MCGWIER-WATTS CHRISTINA PILOT $96,387.39 5 0091 SCOTT HARVEY F. FLTAT $32,278.40 6 0106 THACKER DAVID S. FLTAT $24,161.14 7 0355 BELL THOMAS B. PILOT $59,803.16 8 0366 GLENN MARTHA S. PILOT $120,202.38

  4. Overview of PROC PRINT You can display • column totals • column subtotals • page breaks for each subgroup. The SAS System ------------------------ JobCode=FLTAT ------------------------- Emp Obs ID LastName FirstName Salary 1 0040 WILLIAMS ARLENE M. 23666.12 2 0071 PERRY ROBERT A. 21957.71 3 0091 SCOTT HARVEY F. 32278.40 4 0106 THACKER DAVID S. 24161.14 ------- --------- JobCode 102063.37

  5. Overview of PROC PRINT The SAS System ------------------------ JobCode=PILOT ------------------------- Emp Obs ID LastName FirstName Salary 5 0031 GOLDENBERG DESIREE 50221.62 6 0082 MCGWIER-WATTS CHRISTINA 96387.39 7 0355 BELL THOMAS B. 59803.16 8 0366 GLENN MARTHA S. 120202.38 ------- --------- JobCode 326614.55 ========= 428677.92

  6. Basic Report General Syntax: PROC PRINT DATA=SAS-data-set; RUN; • List all variables and all observations in the data set • A column for observation numbers appears on the far left. • Variables appear in the order in which they occur in the data set. Example: libname mylib ‘c:\math707\sasdata’; Proc print data = mylib.heart; Run;

  7. Creating a Basic List Report with selected variables General form of the PRINT procedure: Example: PROC PRINT DATA=SAS-data-set;VAR variable(s) ; RUN; libname mylib ‘c:\math707\sasdata'; proc print data=mylib.heart; VAR sex heart cardiac; run;

  8. Removing the Obs Column The NOOBS option suppresses the row numbers on the left side of the report. General form of the NOOBS option: PROC PRINT DATA=SAS-data-set NOOBS;RUN; Example: libname mylib ‘c:\math707\sasdata‘ NOOBS; proc print data=mylib.heart; VAR sex heart cardiac; run;

  9. Identifying Observations in PROC PRINT procedure Using ID statement in PROC PRINT procedure: General form for ID statement: ID variable(s); Example: libname mylib ‘c:\math707\sasdata'; proc print data=mylib.heart; VAR sex heart cardiac; ID patient sex; run; • The variables patient and sex are displayed first in each row, followed by the variables in the VAR statement. • No Obs column appears. • If a variable appears in both ID and VAR statements, the variable will be displayed TWICE. Therefore, a variable in the ID statement does not need to be in the VAR statement.

  10. Exercise The SAS data set Insure is posted on our class webpage.

  11. Exercise Write a program to read the Insure data, and print the data using different proc statements, respectively: • Basic listing print, • Do not print OBS column, and print only variables ID, NAME, Total, BalanceDue • Use BalanceDue and Name as ID, and print BalanceDue, Name, Policy, PctInsured and Total with BalanceDue and Name in the output only once. Save the program, C4_Print1 in SASEx folder

  12. Answer Libname mylib ‘c:\math707\sasdata’; Proc print data= mylib.insure; run; Proc print data=mylib.insure noobs; VAR ID, NAME, Total, BalanceDue; Run; Proc print data=mylib.insure; ID Balancedue Name; VAR Policy, PctInsured Total; Run;

  13. Selecting observations: use WHERE Statement to produce a report of a subset of the data set The WHERE statement • enables you to select observations that meet a certain condition • can be used with most SAS procedures.

  14. WHEREwhere-expression; General form of the WHERE statement: where-expression specifies the conditions for selecting observation. It is a sequence of operands and operators. Operands include • variables • constants. • Operators include • comparison operators • logical operators • special operators • functions.

  15. Comparison Operators used in Where statement Examples of Where statement: where Salary>25000; where EmpID='0082'; where Salary=.; where LastName=' '; where JobCode in('PILOT','FLTAT'); where JobCode in('PILOT' 'FLTAT'); Character comparisons are case-sensitive. The IN operator allows commas or blanks to separate values.

  16. Comparison Operators Mnemonic Symbol Definition

  17. AND if both expressions are true, then the compound expression is true & where JobCode='FLTAT' and Salary>50000; NOT can be combined with other operators to reverse the logic of a comparison. ^ where JobCode not in('PILOT','FLTAT'); Logical Operators Logical operators include OR if either expression is true, then the compound expression is true | where JobCode='PILOT' or JobCode='FLTAT';

  18. BETWEEN-AND selects observations in which the value of the variable falls within a range of values, inclusively. where Salary between50000and70000; CONTAINS selects observations that include the specified substring. ? where LastName ? 'LAM'; (LAMBERT, BELLAMY, and ELAM are selected.) Special Operators used in Where statement Special operators include

  19. Special Operators used in Where statement Additional special operators supported by the WHERE statement are • LIKE • sounds like • IS MISSING (or IS NULL).

  20. Special Operators used in Where statement LIKE : selects observations by comparing character values to specified patterns. A percent sign (%) replaces any number of characters. An underscore (_) replaces one character. where Code like 'E_U%'; Selects observations where the value of Code begins with an E, followed by a single character, followed by a U, followed by any number of characters.

  21. Special Operators used in Where statement The sounds like (=*) operator selects observations that contain spelling variations of the word or words specified. Selects names like SMYTHE and SMITT. IS NULL or IS MISSING selects observations in which the value of the variable is missing. where Name=*'SMITH'; where Flight is missing; where Flight is null;

  22. Printing Selected Observations Use the WHERE statement to control which observations are processed. PROC Step Select rows to print proc print data=mylib.empdata noobs; var JobCode EmpID Salary; where JobCode='PILOT'; run; The SAS System Job Emp Code ID Salary PILOT 0031 50221.62 PILOT 0082 96387.39 PILOT 0355 59803.16 PILOT 0366 120202.38

  23. More examples of Where statement for selecting observations WHERE actlevel = ‘LOW’ or actlevel = ‘HIGH’; WHERE actlevel in (‘LOW’ , ‘HIGH’); WHERE (age >= 40 and pulse > 80) or actlevel = ‘HIGH’; WHERE firstname contains ‘SAM’; Where firstname ? ‘SAM’; Where salary between 50000 and 80000 ;

  24. Exercise: Admit data

  25. Exercise Write a SAS program to read the Admit data and do the following tasks: Print the data with only ActLevel to be LOW or HIGH. Print the data with Age >= 40 and Weight between 160 and 200 Print the data, do not print the OBS column, use Name as ID, print only variables Name, Age, ActLevel and Fee, only print individuals with Name starting with M or name starting with K. Save the program, C4_print2 to your SASEx folder

  26. Answer options firstobs=1 obs=max; procprint data = mylib.admit; where actlevel in ('LOW' 'HIGH'); run; procprint data = mylib.admit; where age ge 40 and weight between 160 and 200; run; procprint data=mylib.admit noobs; ID name; VAR name age actlevel fee; where name like 'M%' or name like 'K%'; run;

  27. Sorting a SAS Data Set By default, PROC PRINT lists the data in the order in which they appear in the data set. To sort your report based in values of a variable, you must sort the data using PROC SORT procedure before using PRINT procedure. The PROC SORT procedure • rearranges the observations in a SAS data set • can create a new SAS data set containing the rearranged observations • can sort on multiple variables • can sort in ascending (default) or descending order • does not generate printed output • treats missing values as the smallest possible value.

  28. Sorting a SAS Data Set PROC SORT DATA=input-SAS-data-set <OUT=output-SAS-data-set>;BY <DESCENDING> by-variable(s);RUN; General form of the PROC SORT step: Examples: proc sort data=mylib.empdata; by Salary;run; proc sort data=mylib.empdata out=work.jobsal; by JobCode descending Salary;run;

  29. PROC SORT • If you do not use OUT = option, the sorted data set will replace the original data set permanently. • By default, data is sorted in ascending order based on the variables(s) in the BY statement. • The key word DESCENDING option sort the data in descending Example: PROC SORT data = mylib.admit out = mylib.admit_Sort; BY DESCENDING actlevel age; RUN; The data set mylib.admit_sort is sorted by actlevel in descending order and then AGE in ascending order. NOTE: DESCENDING only has effect for the variable following the word.

  30. Generating Column Totals The SUM statement produces column totals. General form of the SUM statement: SUM variable(s); The SUM statement also produces subtotals if you print the data in groups.

  31. Generating Column Totals PROC Step Produce column totals proc print data=mylib.empdata noobs; var JobCode EmpID Salary; sum Salary; run; The SAS System Job Emp Code ID Salary PILOT 0031 50221.62 FLTAT 0040 23666.12 FLTAT 0071 21957.71 . . ========= 428677.92

  32. Requesting Subtotals in PROC PRINT General form: BY statement in the PRINT procedure: BY <DESCENDING> variable1 <DESCENDING> variable2 ……. <NOSORTED> ; • NOSORTED option specifies that observations are not necessarily sorted in order. If observations that have the same values for the BY variables are not contiguous, the procedure treats each contiguous set as a separate group. • If you DO NOT use NOSORTED option, then, the data set MUST be sorted by using PROC SORT prior to using the BY statement in the PROC PRINT procedure. • DESCENDING only affect the variable followed. • If you want two variables to be sorted, both in DESCENDING order, you need to specify DESCENDING ahead of each variable, respectively.

  33. Requesting Subtotals and Grand Totals • Print the data set grouped by JobCode with a subtotal for the Salary column for each JobCode. • The data must be sorted in order to print subtotal based on the variable in the BY statement in the following example. proc sort data=mylib.empdata out=work.empdata_s;by JobCode;run; proc print data=work.empdata_s; by JobCode; sum Salary; run; Using a BY statement and a SUM statement together in a PROC PRINT step produces subtotals and grand totals.

  34. Printing Subtotals and Grand Totals The SAS System ------------------------ JobCode=FLTAT ------------------------- Emp Obs ID LastName FirstName Salary 1 0040 WILLIAMS ARLENE M. 23666.12 2 0071 PERRY ROBERT A. 21957.71 3 0091 SCOTT HARVEY F. 32278.40 4 0106 THACKER DAVID S. 24161.14 ------- --------- JobCode 102063.37 ------------------------ JobCode=PILOT ------------------------- Emp Obs ID LastName FirstName Salary 5 0031 GOLDENBERG DESIREE 50221.62 6 0082 MCGWIER-WATTS CHRISTINA 96387.39 7 0355 BELL THOMAS B. 59803.16 8 0366 GLENN MARTHA S. 120202.38 ------- --------- JobCode 326614.55 ========= 428677.92

  35. Requesting Subtotal in Separate Pages General form of the PAGEBY statement: PAGEBY statement to print each subgroup on a separate page. PAGEBYby-variable; proc print data=mylib.empdata; by JobCode; pageby JobCode; sum Salary; run; The PAGEBY statementmust be used with a BY statement. If the BY variable is in Descending order, your need: BY descending variable-name; PAGEBY variable-name; NOTE: Do not need to add descending in PAGEBY statement.

  36. Print Subtotal in separate pages The SAS System ------------------------ JobCode=FLTAT ------------------------- Emp Obs ID LastName FirstName Salary 1 0040 WILLIAMS ARLENE M. 23666.12 2 0071 PERRY ROBERT A. 21957.71 3 0091 SCOTT HARVEY F. 32278.40 4 0106 THACKER DAVID S. 24161.14 ------- --------- JobCode 102063.37

  37. Print Subtotal in separate pages The SAS System ------------------------ JobCode=PILOT ------------------------- Emp Obs ID LastName FirstName Salary 5 0031 GOLDENBERG DESIREE 50221.62 6 0082 MCGWIER-WATTS CHRISTINA 96387.39 7 0355 BELL THOMAS B. 59803.16 8 0366 GLENN MARTHA S. 120202.38 ------- --------- JobCode 326614.55 ========= 428677.92

  38. Exercise Write a SAS program to read Admit data and do the following tasks for your report: • Sort the data by ActLevel in descending order, and save the sorted data in WORK library with name Admit_s. • Print the data set with the subtotal and grand total of Fee variable for each Actlevel in Descending order, and only print variables Name, Age and Fee. • Print the data with the subtotal and grand total of Fee variable for each Actlevel in Descending order, print only variables Name, Age and Fee, then print each subgroup of ActLevel in different pages. Save the program C4_Print_sort_Subtotal in SASEx folder

  39. Answer PROCSORT data=mylib.admit out=admit_s; by descending ActLevel; run; procprint data=admit_s; by descending actlevel; var Name age fee; sum fee; run; procprint data=admit_s; by descending actlevel; pageby actlevel; var name age fee; sum fee; run;

  40. Creating a customized layout with BY Groups and ID variables When the ID and BY statements specify the same variable, • the Obs column is suppressed • the BY line is suppressed • the ID/BY variable prints in the leftmost column • each ID/BY value only prints at the start of each BY group (and on the subtotal line, if a SUM statement is used).

  41. Creating a customized layout with BY Groups and ID variables Specify JobCode in the BY and ID statements to change the report format. proc sort data=mylib.empdata out=mylib.empdata_s; by JobCode; run; proc print data=mylib.empdata_s; by JobCode; id JobCode; sum Salary; run;

  42. Creating a customized layout with BY Groups and ID variables The SAS System Job Emp Code ID LastName FirstName Salary FLTAT 0040 WILLIAMS ARLENE M. 23666.12 0071 PERRY ROBERT A. 21957.71 0091 SCOTT HARVEY F. 32278.40 0106 THACKER DAVID S. 24161.14 ----- --------- FLTAT 102063.37 PILOT 0031 GOLDENBERG DESIREE 50221.62 0082 MCGWIER-WATTS CHRISTINA 96387.39 0355 BELL THOMAS B. 59803.16 0366 GLENN MARTHA S. 120202.38 ----- --------- PILOT 326614.55 ========= 428677.92

  43. Double Spacing List Output One can control the listing output in double spacing by using the option: DOUBLE in PROC PRINT statement: PROC PRINT Data = mylib.admitdouble; Var id age fee; Where actlevel = ‘HIGH’; Run; NOTE: DOUBLE option does not affect HTML output.

  44. Exercise Write a SAS program to read Admit data and do the following tasks for your report: • Sort the data by ActLevel in descending order, and save the sorted data in WORK library with name Admit_s. • Print the data set with the subtotal and grand total of Fee variable for each Actlevel in Descending order, also use Actlevel as the ID variable, and only print variables Name, Age and Fee. • Same as (2) with output in DOUBLE space. Save the program C4_Print_ID_BY in SASEx folder

  45. Answer PROCSORT data=mylib.admit out=admit_s; by descending ActLevel; run; procprint data=admit_s; by descending actlevel; ID actlevel; var Name age fee; sum fee; run; procprint data=admit_s double; by descending actlevel; ID actlevel; var Name age fee; sum fee; run;

  46. Defining Titles and Footnotes • You use titles and footnotes to enhance reports. • General form of the TITLE statement: TITLEn 'text '; General form of the FOOTNOTE statement: FOOTNOTEn'text '; Examples: title1 'Flight Crew Employee Listing'; footnote2 'Employee Review';

  47. Defining Titles and Footnotes • Features of titles: • Titles appear at the top of the page. • The default title is TheSASSystem. • The value of n can be from 1 to 10. • An unnumbered TITLE is equivalent to TITLE1. • Titles remain in effect until they are changed, cancelled, or you end your SAS session. • The null TITLE statement, title;,cancels all titles.

  48. Defining Titles and Footnotes • Features of footnotes: • Footnotes appear at the bottom of the page. • No footnote is printed unless one is specified. • The value of n can be from 1 to 10. • An unnumbered FOOTNOTE is equivalent to FOOTNOTE1. • Footnotes remain in effect until they are changed, cancelled, or you end your SAS session. • The null FOOTNOTE statement, footnote;, cancels all footnotes.

  49. Changing Titles and Footnotes • TITLEn or FOOTNOTEn • replaces a previous title or footnote with the same number • cancels all titles or footnotes with higher numbers.

  50. PROC PRINT Code Resultant Title(s) proc print data=work.march; title1 'The First Line'; title2 'The Second Line'; run; proc print data=work.march; title2 'The Next Line'; run; proc print data=work.march; title 'The Top Line'; run; proc print data=work.march; title3 'The Third Line'; run; proc print data=work.march; title; run; Defining Titles and Footnotes

More Related