[PDF] PROC SQL VS DATA STEP PROCESSING AGENDA • Comparison PDF PROC-SQL-vs.-DATA-Step-Programming.pdf

Joining SAS data using the DATA Step and PROC SQL Many times a series of 2-table joins Create tables, views, and indexes on columns in tables • Create

In PROC SQL two or more data sets may be vertically CREATE TABLE DATA99 AS SELECT * An 'inner' join is a logical AND of the two tables and

[PDF] 035-2009: Exploring PROC SQL Joins and Join - SAS Support

The SQL procedure is a simple and flexible tool for joining tables of data together When it comes to performing PROC SQL joins, users supply the names of the tables for joining along with the join CREATING A CARTESIAN PRODUCT

[PDF] Proc SQL, the Data Step Killer - SAS

Proc SQL query can join only two datasets at a time (although inner joins without the Although it says create table, it is actually creating a SAS dataset • PROC

[PDF] Etape Data (Merge) ou SAS SQL (Join)?? - Amin Guerss

❖Si les tables ne contiennent pas les mêmes noms de variables, l'étape data ne peut pas être utilisée (possibilité d'utiliser proc SQL) ❖S'assurer qu'il n'y a

[PDF] Using Data Step MERGE and Proc SQL JOIN to Combine SAS

sources and create a convenient store of information in one location is one of the best Whether you merge data via the SAS data step or you join data via PROC properly performing different types of joins in SQL (inner vs outer join, left vs

[PDF] PROC SQL Tips and Techniques - LexJansen

Proc SQL; create table/view newdsname as select var1, var2, varN Unlike ' regular' SAS procedures and Data steps, SQL likes commas In DATA step terms, an inner join on two incoming datasets is equivalent to using a MERGE

[PDF] STT1682 – Progiciels en Statistique et Actuariat Cours 8 – PROC SQL

SAS a créé une procédure appelé le PROC SQL très puissante utilisant une À travers un PROC SQL, nous serons CREATE TABLE BASESORTANTE AS qu'on a réussit à lier de BASE2, on va effectuer un LEFT JOIN représenté par le

[PDF] Procédure SQL de SAS

Commandes proc sql ; alter table déclaration de modification ; Page 2 2 Procédure SQL de SAS create table déclaration de création ; delete décl-

[PDF] PROC SQL VS DATA STEP PROCESSING AGENDA • Comparison

Joining SAS data using the DATA Step and PROC SQL Many times a series of 2-table joins Create tables, views, and indexes on columns in tables • Create

[PDF] SUGI 26: Joining SAS(r) and DBMS Tables Efficiently - BeOptimized

libname dbms db2 dsn=tvdb uid=helf pwd=mypw; proc SQL; create table History as select * from Warranty a, dbms MfgHist (dbkey=sn) b

[PDF] sas proc sql create table like

[PDF] sas proc sql create table replace

[PDF] sas proc sql create table syntax

[PDF] sas proc sql format

[PDF] sas proc sql; create table as select example

[PDF] sas retain array

[PDF] sas sum(of array name * )

[PDF] sascrunch arrays

[PDF] sassen cities in a world economy pdf

[PDF] sassen the global city new york london tokyo

[PDF] sat interventions

[PDF] sat practice test 1

[PDF] sat practice test 1 answers

[PDF] sat practice test 10 answers

[PDF] sat practice test 5

2/26/2015

PROC SQL VS. DATA STEP PROCESSING

Mary-Elizabeth ("M-E") Eddlestone

Principal Analytics Engineer, SAS Customer Loyalty

Customer Engagement & Support

AGENDA

•Comparison of DATA Step and PROC SQL capabilities •Joining SAS data using the DATA Step and PROC SQL

2/26/2015

COMPARISON OF DATA STEP AND PROC SQL

CAPABILITIES

DATA STEP VS.

PROC SQL

CAPABILITIES COMPARISON

•DATA Step is typically sequential processing •Rows of data are processed in the same order they started in •Row 1, row 2, row 3, etc. •Output for most joins will have a consistent order •Can do random access using POINT= or KEY= options on SET statement •PROC SQL uses an optimizer - dissimilar results •SQL has no concept of row order - does not process sequentially •Rows can be, and often are, returned in a random order unless an ORDER

BY clause is used

•GROUP BY does not include ORDER BY SQL Optimizer explained: http://support.sas.com/techsup/technote/ts553.html

2/26/2015

DATA STEP VS.

PROC SQL

DATA Step

•No issues as most processing for single tables as well as joins is sequentialPROC SQL •Lags in performance without help •Always sort large tables before joining in SQL •NOT indexed! •Many times a series of 2-table joins will out-perform a multi-table (3+) join in PROC SQL Copyright © 2014, SAS Institute Inc. All rights reserved.

DATA STEP

WHAT CAN I DO WITH IT?

•Creating SAS data sets (SAS data files or SAS views) •Creating SAS data sets from input files that contain raw data (external files)

•Creating new SAS data sets from existing ones by subsetting, merging, modifying, and updating existing SAS data sets

•Analyzing, manipulating, or presenting your data •Computing the values for new variables •Report writing, or writing files to disk or tape •Retrieving information •File management

2/26/2015

PROC SQL

•ANSI standard SQL is the base •SAS-isms added (functions, formats, labels, etc.)

PROC SQL;

SELECT<* | col1<, col2<..., coln>>>

FROM << as> alias-name>

PROC SQL

WHAT CAN I DO WITH IT?

•Retrieve and manipulate data that is stored in tables or views. •Create tables, views, and indexes on columns in tables. •Create SAS macro variables that contain values from rows in a query's result.

•Add or modify the data values in a table's columns or insert and delete rows. You can also modify the table itself by adding, modifying, or dropping columns.

•Send DBMS-specific SQL statements to a database management system (DBMS) and retrieve DBMS data.

2/26/2015

CapabilityDATA Step PROC SQL

Creating SAS data sets (SAS data files or SAS views) X X

Create Indexes on tablesX

Creating SAS data sets from input files that contain raw data (external files) X Analyzing, manipulating, or presenting your data X X (listing reports)

Writing external files to disk or tape X

Computing the values for new variables X X

Retrieving system information X

File management X

Create SAS macro variables that contain values from rows in a query's result X X Send DBMS-specific SQL statements to a database management system (DBMS) and retrieve DBMS dataX Copyright © 2014, SAS Institute Inc. All rights reserved.

CapabilityDATA Step PROC SQL

Use DO loopsX

Use ArraysX

IF ... THEN ... ELSE processing X X

Use Object Oriented programming with JAVA or Hash objects X

2/26/2015

PROCESSING COMPARISONS

CONDITIONAL

PROCESSING

•CASE expression in SQL •IF THEN statement in the DATA step •Very flexible

2/26/2015

PROC SQL

CONDITIONAL PROCESSING: CASE EXPRESSION

proc sql; select name, case when continent = 'North America' then 'US' when continent = 'Oceania' then 'Pacific

Islands'

DATA STEP

CONDITIONAL PROCESSING: IF THEN STATEMENT

data new; set states; ifcontinent = 'North America' thenregion ='US'; elseifcontinent = 'Oceania' thenregion = 'Pacific Islands'; elseregion='None'; run;

2/26/2015

INDEXES

PROC SQL

proc sql; drop index providerId from health.test; create unique index ProviderID on health.provider(providerID); PROC SQL can be used to create and administer indexes. Copyright © 2014, SAS Institute Inc. All rights reserved.

CREATING

MACRO

VARIABLES

PROC SQL

proc sqlnoprint; select distinctstyle into :style01 -:style04 fromwork.houses; %putThere were &sqlobsdistinct values.;

PARTIAL LOG:

%put There were &sqlobs distinct values.;

There were 4 distinct values.

2/26/2015

CREATING

MACRO

VARIABLES

PROC SQL

proc sqlnoprint; select distinctstyle into :style01 - fromwork.houses; %putThere were &sqlobs distinct values.;

PARTIAL LOG:

%put There were &sqlobs distinct values.;

There were 4 distinct values.

DATA STEPCREATING MACRO VARIABLES

data _null_; call symputx(' items ', ' text to assign'); call symputx(' x ', 123.456); run; Both the DATA step and SQL can create macro variables at execution time.

The DATA step might be considered more flexible.

2/26/2015

DATA STEPRETRIEVING SYSTEM INFORMATION

DATA STEP

RETRIEVING SYSTEM INFORMATION

DOPENopens a directory, and returns a directory identifier value. datadiropts; lengthfoption $ 12charval $ 40; keepfoption charval; rc=filename("mydir", "physical-name"); did = dopen("mydir"); numopts=doptnum(did); doi=1tonumopts; foption=doptname(did,i); charval=dinfo(did,foption); output; end; run;

2/26/2015

DATA STEP

RETRIEVING SYSTEM INFORMATION

DOPTNUMreturns the number of informational items that are available for a directory. datadiropts; lengthfoption $ 12charval $ 40; keepfoption charval; rc = filename("mydir", "physical-name"); did = dopen("mydir"); numopts = doptnum(did); doi = 1tonumopts; foption = doptname(did,i); charval = dinfo(did,foption); output; end; run; Copyright © 2014, SAS Institute Inc. All rights reserved.

DATA STEP

RETRIEVING SYSTEM INFORMATION

DOPTNAMEreturns directory attribute information.

datadiropts; lengthfoption $ 12charval $ 40; keepfoption charval; rc = filename("mydir", "physical-name"); did = dopen("mydir"); numopts=doptnum(did); doi = 1tonumopts; foption = doptname(did,i); charval = dinfo(did,foption); output;quotesdbs_dbs17.pdfusesText_23

[PDF] [PDF] PROC SQL VS DATA STEP PROCESSING AGENDA • Comparison

2/26/2015

PROC SQL VS. DATA STEP PROCESSING

Mary-Elizabeth ("M-E") Eddlestone

Customer Engagement & Support

AGENDA

2/26/2015

COMPARISON OF DATA STEP AND PROC SQL

CAPABILITIES

DATA STEP VS.

PROC SQL

CAPABILITIES COMPARISON

BY clause is used

2/26/2015

DATA STEP VS.

PROC SQL

DATA Step

DATA STEP

WHAT CAN I DO WITH IT?

2/26/2015

PROC SQL

PROC SQL;

SELECT<* | col1<, col2<..., coln>>>

FROM << as> alias-name>

PROC SQL

WHAT CAN I DO WITH IT?

2/26/2015

CapabilityDATA Step PROC SQL

Create Indexes on tablesX

Writing external files to disk or tape X

Computing the values for new variables X X

Retrieving system information X

File management X

CapabilityDATA Step PROC SQL

Use DO loopsX

Use ArraysX

IF ... THEN ... ELSE processing X X

2/26/2015

PROCESSING COMPARISONS

CONDITIONAL

PROCESSING

2/26/2015

PROC SQL

CONDITIONAL PROCESSING: CASE EXPRESSION

Islands'

DATA STEP

CONDITIONAL PROCESSING: IF THEN STATEMENT

2/26/2015

INDEXES

PROC SQL

CREATING

VARIABLES

PROC SQL

PARTIAL LOG:

There were 4 distinct values.

2/26/2015

CREATING

VARIABLES

PROC SQL

PARTIAL LOG:

There were 4 distinct values.

DATA STEPCREATING MACRO VARIABLES

The DATA step might be considered more flexible.

2/26/2015

DATA STEPRETRIEVING SYSTEM INFORMATION

DATA STEP

RETRIEVING SYSTEM INFORMATION

2/26/2015

DATA STEP

RETRIEVING SYSTEM INFORMATION

DATA STEP

RETRIEVING SYSTEM INFORMATION

DOPTNAMEreturns directory attribute information.