This documentation explores various methods for integrating external data sources with SAS© Viya™ 4 and SAS© Studio using SAS© engines. It demonstrates how to import comma-delimited files and Microsoft Excel data using PROC IMPORT and specific SAS©/ACCESS engines like XLSX. Examples also cover creating and manipulating data within external DBMS (such as Teradata) using SAS© DATA steps and LIBNAME statements. Advanced techniques such as embedding SAS©/ACCESS LIBNAME statements within PROC SQL views and using the SQL Pass-Through facility for direct DBMS queries are illustrated. Additionally, the document provides guidance on importing structured data formats like XML and JSON using their respective SAS© engines (XMLV2 and JSON). Each example emphasizes the importance of configuring appropriate SAS©/ACCESS interfaces and managing variable naming conventions for seamless data integration.
Data Analysis
Type : CREATION_INTERNE
Examples use generated data (datalines) or SASHELP, or require the user to provide an external file (CSV, Excel, XML, JSON).
1 Code Block
PROC IMPORT Data
Explanation :
This example uses the FILENAME statement to assign a fileref to a temporary file. The HTTP procedure specifies the URL of a CSV file and writes the data to the fileref. PROC IMPORT then reads the comma-delimited data and creates a SAS data set. Finally, PROC PRINT displays the imported data set, confirming the successful import of data and column names.
This example uses the FILENAME statement to assign a fileref to a temporary file. The HTTP procedure specifies the URL of a CSV file and writes the data to the fileref. PROC IMPORT then reads the comma-delimited data and creates a SAS data set. Finally, PROC PRINT displays the imported data set, confirming the successful import of data and column names.
Copied!
| 1 | filename chol temp; |
| 2 | PROC HTTP |
| 3 | url="http://support.sas.com/documentation/onlinedoc/viya/exampledatasets/cholesterol.csv" |
| 4 | out=chol; |
| 5 | QUIT; |
| 6 | PROC IMPORT datafile=chol |
| 7 | out=work.mycholesterol |
| 8 | dbms=csv |
| 9 | replace; |
| 10 | RUN; |
| 11 | PROC PRINT DATA=work.mycholesterol; |
| 12 | RUN; |
2 Code Block
PROC IMPORT Data
Explanation :
This example imports Excel data using the XLSX engine and PROC IMPORT. The VALIDVARNAME=V7 option is used to convert column names to SAS-compatible variable names (spaces are converted to underscores). The input file is specified via the DATAFILE= option, the database type is set to XLSX, and the output dataset is created in the WORK library. The REPLACE option allows overwriting an existing SAS data set.
This example imports Excel data using the XLSX engine and PROC IMPORT. The VALIDVARNAME=V7 option is used to convert column names to SAS-compatible variable names (spaces are converted to underscores). The input file is specified via the DATAFILE= option, the database type is set to XLSX, and the output dataset is created in the WORK library. The REPLACE option allows overwriting an existing SAS data set.
Copied!
| 1 | options validvarname=v7; |
| 2 | PROC IMPORT datafile='file-path/cholesterol.xlsx' |
| 3 | dbms=xlsx |
| 4 | out=work.mycholesterol |
| 5 | replace; |
| 6 | RUN; |
3 Code Block
DATA STEP Data
Explanation :
This example shows how a SAS DATA step can create a Teradata table. The LIBNAME statement assigns a libref 'mytddata' to the Teradata engine and specifies connection options. The DATA step creates a table named 'grades' in the Teradata DBMS using inline data (datalines). Finally, PROC DATASETS is used to display information about the 'mytddata' library, confirming that the engine is Teradata and 'grades' is a DBMS table.
This example shows how a SAS DATA step can create a Teradata table. The LIBNAME statement assigns a libref 'mytddata' to the Teradata engine and specifies connection options. The DATA step creates a table named 'grades' in the Teradata DBMS using inline data (datalines). Finally, PROC DATASETS is used to display information about the 'mytddata' library, confirming that the engine is Teradata and 'grades' is a DBMS table.
Copied!
| 1 | LIBNAME mytddata teradata server=mytera user=myid password=mypw; |
| 2 | DATA mytddata.grades; |
| 3 | INPUT student $ test1 test2 final; |
| 4 | DATALINES; |
| 5 | Fred 66 80 70 |
| 6 | Wilma 97 91 98 |
| 7 | ; |
| 8 | PROC DATASETS library=mytddata; |
| 9 | RUN; |
| 10 | QUIT; |
4 Code Block
PROC SQL
Explanation :
This example embeds a SAS/ACCESS LIBNAME statement directly within a PROC SQL view. The initial LIBNAME statement assigns a libref to the SAS view's storage location. The SQL procedure then creates a view named 'mygrades' by selecting all columns from the 'mytddata.grades' table. The 'USING LIBNAME' statement embeds the Teradata connection details into the view definition. Finally, PROC PRINT executes this view, retrieving data from the Teradata table via the embedded connection.
This example embeds a SAS/ACCESS LIBNAME statement directly within a PROC SQL view. The initial LIBNAME statement assigns a libref to the SAS view's storage location. The SQL procedure then creates a view named 'mygrades' by selecting all columns from the 'mytddata.grades' table. The 'USING LIBNAME' statement embeds the Teradata connection details into the view definition. Finally, PROC PRINT executes this view, retrieving data from the Teradata table via the embedded connection.
Copied!
| 1 | LIBNAME viewlib v9 'library-path'; |
| 2 | PROC SQL; |
| 3 | create view viewlib.mygrades as |
| 4 | select * |
| 5 | from mytddata.grades |
| 6 | using LIBNAME mytddata teradata |
| 7 | server=mytera |
| 8 | user=myid password=mypw; |
| 9 | QUIT; |
| 10 | PROC PRINT DATA=viewlib.mygrades noobs; |
| 11 | RUN; |
5 Code Block
PROC SQL
Explanation :
This PROC SQL example uses the SQL Pass-Through facility to send a query directly to a Teradata table. The CONNECT statement establishes a connection to the database. The FROM 'CONNECTION TO myconn' clause is used to execute a DBMS-specific SQL query (here, selecting final grades greater than 90 from the 'grades' table). Finally, the DISCONNECT statement ends the database connection.
This PROC SQL example uses the SQL Pass-Through facility to send a query directly to a Teradata table. The CONNECT statement establishes a connection to the database. The FROM 'CONNECTION TO myconn' clause is used to execute a DBMS-specific SQL query (here, selecting final grades greater than 90 from the 'grades' table). Finally, the DISCONNECT statement ends the database connection.
Copied!
| 1 | PROC SQL; |
| 2 | connect to teradata as myconn (server=mytera |
| 3 | user=myid password=mypw); |
| 4 | select * |
| 5 | from connection to myconn |
| 6 | (select * |
| 7 | from grades |
| 8 | where final gt 90); |
| 9 | disconnect from myconn; |
| 10 | QUIT; |
6 Code Block
PROC PRINT Data
Explanation :
This example uses the SAS XMLV2 engine to import XML data. The first FILENAME statement assigns a fileref to the input XML file. The second assigns a fileref to the location where an XMLMap file will be generated. The LIBNAME statement associates the libref with the XMLV2 engine, specifying automatic XMLMap generation. PROC PRINT is then used to display parts of the imported data, verifying the successful import of the XML into an in-memory SAS data set.
This example uses the SAS XMLV2 engine to import XML data. The first FILENAME statement assigns a fileref to the input XML file. The second assigns a fileref to the location where an XMLMap file will be generated. The LIBNAME statement associates the libref with the XMLV2 engine, specifying automatic XMLMap generation. PROC PRINT is then used to display parts of the imported data, verifying the successful import of the XML into an in-memory SAS data set.
Copied!
| 1 | filename nhl 'file-path/nhl.xml'; |
| 2 | filename map 'file-path/nhlgenerate.map'; |
| 3 | LIBNAME nhl xmlv2 automap=replace xmlmap=map; |
| 4 | PROC PRINT DATA=nhl.team noobs; |
| 5 | var TEAM_name TEAM_abbrev; |
| 6 | RUN; |
7 Code Block
PROC DATASETS Data
Explanation :
This example uses the SAS JSON engine to read JSON data. The LIBNAME statement assigns a libref 'mydata' to the JSON engine and specifies the path to the input JSON file. The PROC DATASETS procedure is then used to display the in-memory SAS datasets created from the JSON file, demonstrating the import of JSON data as temporary SAS datasets.
This example uses the SAS JSON engine to read JSON data. The LIBNAME statement assigns a libref 'mydata' to the JSON engine and specifies the path to the input JSON file. The PROC DATASETS procedure is then used to display the in-memory SAS datasets created from the JSON file, demonstrating the import of JSON data as temporary SAS datasets.
Copied!
| 1 | LIBNAME mydata json '/file-path/example.json'; |
| 2 | PROC DATASETS lib=mydata; |
| 3 | RUN; |
| 4 | QUIT; |
This material is provided "as is" by We Are Cas. There are no warranties, expressed or implied, as to merchantability or fitness for a particular purpose regarding the materials or code contained herein. We Are Cas is not responsible for errors in this material as it now exists or will exist, nor does We Are Cas provide technical support for it.
Copyright Info : Copyright © SAS Institute Inc. All Rights Reserved
Related Documentation : Data Access
| Sujet / Mot-clé | Lien vers la ressource |
|---|---|
| DOC FedSQL | en/sampleCode/FEDSQL9D66 |
Expert Advice
Michael
Responsable de l'infrastructure Viya.
« The strength of SAS Viya lies in its Multiple Engine Architecture, which allows SAS to act as a universal translator for data. Whether you are pulling a simple CSV from a web URL or querying a multi-terabyte Teradata warehouse, the key to performance is choosing the right integration method: Libname Engines versus SQL Pass-Through. »