Transcript Enhanced Editor Enhanced

www.OASUS.ca
Getting Familiar with
DS2 and FedSQL
Greg Ludwinski
Statistics Canada
www.OASUS.ca
FedSQL
• SAS Implementation of ANSI SQL:1999
core standard (vender neutral)
• Fed stands for Federated
• Accessible in SAS base via Proc
FedSQL starting in SAS 9.4
• Optimized with multi-threaded
algorithms
www.OASUS.ca
DS2
• A programming language accessible
via Proc DS2 in base SAS
• Accepts embedded FedSQL syntax
• Many similarities to the data step
• Leverages a new SAS kernel
• In-Database Code Accelerator
www.OASUS.ca
Data Types
Category
Types
Character
CHAR(n) , VARCHAR(n)
NCHAR(n), NVARCHAR(n)
Fractional numeric
FLOAT(p), REAL, DOUBLE
DECIMAL(p,s)
Integer numeric
TINYINT (127)
SMALLINT (32,767)
INTEGER (2,147,483,647)
BIGINT ( 9,223,372,036,854,775,807)
Binary
BINARY(n)
VARBINARY(n)
Date and time
ANSI date, time, and timestamp,
Conversion functions;
TO_DOUBLE, TO_DATE, TO_TIME,
TO_TIMESTAMP
www.OASUS.ca
•
•
•
•
•
Programming Blocks
DATA...ENDDATA
METHOD...END
PACKAGE...ENDPACKAGE
THREAD...ENDTHREAD
DO...END
www.OASUS.ca
Program Structure
Data <output dataset | _NULL_>;
{global variable declarations}
method init();
{local variable declarations, DS2 Statements}
end;
method run();
{local variable declarations, DS2 Statements}
end;
method term();
{local variable declarations, DS2 Statements}
end;
enddata;
www.OASUS.ca
Methods
• All code executes in a method
• System methods
– Init(), Run(), Term(), SetParms()
• User-defined methods
• Methods can be overloaded
www.OASUS.ca
Packages
DS2 packages are similar to classes
Predefined
1. FCMP
2. SQLSTMT
3. Hash
User defined
4. Logger
5. Matrix
6. HTTP
www.OASUS.ca
Threads
• Light weight processes
• Share resources
• Process sections of a dataset
concurrently
• Executed with the SET FROM
statement
www.OASUS.ca
Scope
• Declare variables (DS2SCOND=ERROR)
• Global scope vs. Method scope
• Data program vs. Package vs. Thread
scope.
• Dot notation resolves conflicts (This)
www.OASUS.ca
DS2 Benefits
1.Precision
2.Modularity, Encapsulation, and
Abstraction
3.Multi-threading
4.Performance
www.OASUS.ca
•
•
•
•
•
Resources
DS2 Language Reference (1000+ pages)
FedSQL Language Reference (500+ pages)
SGF 2014 Papers
DS2 and You (SGF 2013)
SAS Support
*DS2 Programming Essentials course is coming
to Ottawa in 2015
www.OASUS.ca
Example Time
Questions / Comments
www.OASUS.ca
Greg Ludwinski
Project Leader
System Engineering Division
Statistics Canada
Jean Talon Building 5th Floor Section A2
170, Tunney’s Pasture driveway
Ottawa, Ont., K1A 0T6
(613) 951-2768
[email protected]