proc sql select codes to master for power programming codes and examples from sas com n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
PROC SQL – Select Codes To Master For Power Programming Codes and Examples from SAS.com PowerPoint Presentation
Download Presentation
PROC SQL – Select Codes To Master For Power Programming Codes and Examples from SAS.com

Loading in 2 Seconds...

play fullscreen
1 / 48

PROC SQL – Select Codes To Master For Power Programming Codes and Examples from SAS.com - PowerPoint PPT Presentation


  • 123 Views
  • Uploaded on

PROC SQL – Select Codes To Master For Power Programming Codes and Examples from SAS.com. Nethra Sambamoorthi, PhD Northwestern University Master of Science in Predictive Analytics Program . Data Processing Terminologies Across Data Sciences…. Why PROC SQL or What Can It Do For Analysts?.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'PROC SQL – Select Codes To Master For Power Programming Codes and Examples from SAS.com' - erica


Download Now 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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
proc sql select codes to master for power programming codes and examples from sas com

PROC SQL – Select Codes To Master For Power Programming Codes and Examples from SAS.com

Nethra Sambamoorthi, PhD

Northwestern University

Master of Science in Predictive Analytics Program

why proc sql or what can it do for analysts
Why PROC SQL or What Can It Do For Analysts?
  • Generate reports
  • Generate summary statistics
  • Retrieve data from tables or views
  • Combine data from tables or views
  • Create tables, views, and indexes
  • Update the data values in PROC SQL tables
  • Update and retrieve data from database management system (DBMS) tables
  • Modify a PROC SQL table by adding, modifying, or dropping columns
  • PROC SQL can be used in an interactive SAS session or within batch programs, and it
  • Can include global statements, such as TITLE and OPTIONS.
an example of extracting summarizing and printing using data step
An Example of Extracting, Summarizing, and Printing Using Data Step

title 'Large Countries Grouped by Continent';

proc summary data=sql.countries;

where Population > 1000000;

class Continent;

var Population;

output out=sumPopsum=TotPop; run;

procsort data=SumPop;

by totPop; run;

procprint data=SumPopnoobs;

var Continent TotPop;

format TotPop comma15.;

where _type_=1; run;

/* Extracting and summarizing */

/* Sorting to arrange the output */

/* Printing */

creating the same using proc sql
Creating The Same Using PROC SQL

procsql;

title 'Population of Large Countries Grouped by Continent';

select Continent, sum(Population) as TotPop format=comma15.

from sql.countries

where Population gt 1000000

group by Continent

order by TotPop;

quit;

three important aspects describe print quit
Three Important Aspects – Describe, Print, Quit

/* Helps understand the structure of the table */

PROC SQL;

Describe table sql.unitedstates;

Quit;

select means printing is included unless
SELECT means PRINTING is Included Unless
  • SELECT * /* all columns */
  • SELECT city, state /* specific columns */
  • SELECT distinct continent /* specific columns but avoid dup */

So it is possible to run this

retrieving data from multiple tables
Retrieving Data From Multiple Tables
  • Means we are JOINING tables
  • If there is no JOIN statement, it means (1) Cartesian product of records [no subset condition ] or (2) inner joins [ we need some subset condition]
  • Alias names can be used for tables too; it helps simplify calling specific columns of a table
order the output from inner join
Order the output from INNER JOIN

INNER JOIN can be used explicitly

columns are directly comparable between two tables
Columns are directly comparable between two tables…

Capitals FROM sql.unitedstates

City FROM sql.uscitycoord

Postalcodes FROM sql.postalcodes

slide35
NATURAL is applicable for both LEFT and RIGHT JOIN. The purpose is to reduce verbose to match on multiple common columns…

Gives the same output;

Non matching rows have missing values