CSULB_STAT475_handout21

CSULB_STAT475_handout21 - STAT 475 Chapter 2 PERFORMING...

Info iconThis preview shows pages 1–4. Sign up to view the full content.

View Full Document Right Arrow Icon
STAT 475 Chapter 2 PERFORMING ADVANCED QUERIES USING PROC SQL Reference SAS Certification Prep Guide: Advanced Programming for SAS 9 To display all columns in the order in which they are stored in a table, use an asterisk (*) in the SELECT statement. Example. In the class roster example, the following SELECT step displays all columns and rows in a table. data roster; input name $ @11 id $ final total grade $; cards; Hano 9336 92 98 A Kelbert 9564 96 95 A Le 2143 98 93 A Allen 5686 83 92 A Chen 1414 67 90 A Chua 7573 82 89 B Disbrow 7497 58 88 B Seo 5666 65 88 B Hogan 7730 64 87 B Lacap 6734 83 84 B Jin 8307 71 84 B Ciralli 7002 53 78 C Karni 8793 65 78 C Martinez 7057 28 77 C Davis 5392 58 77 C Franjic 8455 0 53 I Shimazaki 7439 40 63 D Uy 4186 0 20 F ; proc sql; select * from roster; quit; The output is 1
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
name id final total grade ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ Hano 9336 92 98 A Kelbert 9564 96 95 A Le 143 98 93 A Allen 5686 83 92 A Chen 1414 67 90 A Chua 7573 82 89 B Disbrow 7497 58 88 B Seo 666 65 88 B Hogan 7730 64 87 B Lacap 6734 83 84 B Jin 8307 71 84 B Ciralli 7002 53 78 C Karni 8793 65 78 C Martinez 7057 28 77 C Davis 5392 58 77 C Franjic 8455 0 53 I Shimazak 7439 40 63 D Uy 4186 0 20 F When using asterisk (*) in SELECT step, it is advisable to specify the FEEDBACK option in the PROC SQL statement, which writes the expanded list of columns in the SAS log window. Example. In the above example, type proc sql feedback; select * from roster; quit; The output of the log window contains proc sql feedback; select * from roster; NOTE: Statement transforms to: select ROSTER.name, ROSTER.id, ROSTER.final, ROSTER.total, ROSTER.grade from WORK.ROSTER; quit; To indicate the maximum number of rows to be displayed, use the OUTOBS= option in the PROC SQL statement. Example. In the above example, to output only the first 5 rows, type 2
Background image of page 2
proc sql outobs=5; select * from roster; quit; The output is name id final total grade ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ Hano 9336 92 98 A Kelbert 9564 96 95 A Le 143 98 93 A Allen 5686 83 92 A Chen 1414 67 90 A To eliminate duplicate rows from the query, use the keyword DISTINCT in the SELECT statement. The DISTINCT keyword applies to all columns that are listed in the SELECT statement. Example. The data set below contains bank account ID, dates of transactions and type of transaction (deposit or withdrawal). The DISTINCT keyword is used to display only the distinct bank account IDs. data bank; input @1 accountID $ @6 trans_date MMDDYY10. @17 trans_name $10.; cards; 5367 01/03/2011 withdrawal 5367 01/03/2011 deposit 5367 01/13/2011 deposit 9445 01/01/2011 withdrawal 9445 01/04/2011 withdrawal 9445 01/07/2011 deposit 7427 01/02/2011 deposit 7427 01/03/2011 deposit 7427 01/03/2011 deposit 7427 01/05/2011 withdrawal ; proc sql; select distinct accountID from bank; quit; 3
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Image of page 4
This is the end of the preview. Sign up to access the rest of the document.

Page1 / 12

CSULB_STAT475_handout21 - STAT 475 Chapter 2 PERFORMING...

This preview shows document pages 1 - 4. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online