Data access is a fundamental task in SAS© programming. This guide covers reading external files via the INFILE statement and writing via the FILE statement, by directly specifying the physical path or by using filerefs for indirect reference. Efficient management of multiple files is also presented through assigning filerefs to aggregate storage locations. Advanced access methods such as CATALOG, DATAURL, FTP, Hadoop, SOCKET, URL, and WebDAV are listed. A section is dedicated to reading binary data using specific SAS© informats, emphasizing RECFM and LRECL parameters for column-wise binary data. Finally, examples are included to optimize data loading into CAS with the DVR format.
Data Analysis
Type : MIXTE
Examples use generated data (datalines) for CAS demonstrations, but other examples depend on external files not created in the code (marked external_data_not_found: 1).
1 Code Block
DATA STEP
Explanation :
This example shows how to read raw data from an external file using the INFILE and INPUT statements. INFILE is required because the source data comes from an external text file.
This example shows how to read raw data from an external file using the INFILE and INPUT statements. INFILE is required because the source data comes from an external text file.
Copied!
| 1 | DATA weight; |
| 2 | INFILE |
| 3 | INPUT PatientID $ Week1 Week8 Week16; |
| 4 | loss=Week1-Week16; |
| 5 | RUN; |
2 Code Block
DATA STEP
Explanation :
Specifies the file containing the input data directly using its physical name in quotes.
Specifies the file containing the input data directly using its physical name in quotes.
Copied!
| 1 | DATA weight; |
| 2 | INFILE 'input-file'; |
| 3 | INPUT idno $ week1 week16; |
| 4 | loss=week1-week16; |
| 5 | RUN; |
3 Code Block
DATA STEP
Explanation :
Identifies the file to which the PUT statement writes data. This example shows writing conditions based on a 'loss' variable.
Identifies the file to which the PUT statement writes data. This example shows writing conditions based on a 'loss' variable.
Copied!
| 1 | file 'output-file'; |
| 2 | DATA STATUS; |
| 3 | IF loss ge 5 and loss le 9 THEN |
| 4 | put idno loss 'AWARD STATUS=3'; |
| 5 | ELSE IF loss ge 10 and loss le 14 THEN |
| 6 | put idno loss 'AWARD STATUS=2'; |
| 7 | ELSE IF loss ge 15 THEN |
| 8 | put idno loss 'AWARD STATUS=1'; |
| 9 | RUN; |
4 Code Block
%INCLUDE statement
Explanation :
Allows you to incorporate statements or raw data from another file into your SAS job and execute them.
Allows you to incorporate statements or raw data from another file into your SAS job and execute them.
Copied!
| 1 | %include 'source-file'; |
5 Code Block
DATA STEP / FILENAME statement
Explanation :
Assigns a fileref 'mydata' to an input file, then uses this fileref in the INFILE statement to read the data.
Assigns a fileref 'mydata' to an input file, then uses this fileref in the INFILE statement to read the data.
Copied!
| 1 | filename mydata 'input-file'; |
| 2 | DATA weight; |
| 3 | INFILE mydata; |
| 4 | INPUT idno $ week1 week16; |
| 5 | loss=week1-week16; |
| 6 | RUN; |
6 Code Block
PROC IMPORT / FILENAME statement
Explanation :
Assigns a fileref 'mydata' to an input file, then uses PROC IMPORT to import the data and PROC PRINT to display it.
Assigns a fileref 'mydata' to an input file, then uses PROC IMPORT to import the data and PROC PRINT to display it.
Copied!
| 1 | filename mydata 'input-file'; |
| 2 | PROC IMPORT datafile=mydata |
| 3 | out=shoes dbms=dlm replace; |
| 4 | RUN; |
| 5 | PROC PRINT DATA=shoes; RUN; |
7 Code Block
DATA STEP / FILENAME statement
Explanation :
Assigns a fileref 'myreport' to an output file and a fileref 'mydata' to an input file, then reads the data to create a new dataset.
Assigns a fileref 'myreport' to an output file and a fileref 'mydata' to an input file, then reads the data to create a new dataset.
Copied!
| 1 | filename myreport 'output-file'; |
| 2 | DATA myreport; |
| 3 | INFILE mydata; |
| 4 | INPUT idno $ week1 week16; |
| 5 | loss=week1-Week16; |
| 6 | RUN; |
8 Code Block
PROC EXPORT Data
Explanation :
Exports the SASHELP.SHOES dataset to the external file referenced by 'myreport', using a space delimiter.
Exports the SASHELP.SHOES dataset to the external file referenced by 'myreport', using a space delimiter.
Copied!
| 1 | PROC EXPORT DATA=sashelp.shoes |
| 2 | outfile=myreport dbms=dlm replace; |
| 3 | delimiter=' '; |
| 4 | RUN; |
9 Code Block
FILENAME statement
Explanation :
Assigns a fileref 'mypgm' to a file containing program statements.
Assigns a fileref 'mypgm' to a file containing program statements.
Copied!
| 1 | filename mypgm 'source-file'; |
10 Code Block
FILENAME statement
Explanation :
Assigns a fileref 'myprinter' to an output device specified by its type and host options.
Assigns a fileref 'myprinter' to an output device specified by its type and host options.
Copied!
| 1 | filename myprinter |
| 2 | |
| 3 |
11 Code Block
DATA STEP
Explanation :
Uses the fileref 'mydata' (previously assigned) in the INFILE statement to read the data.
Uses the fileref 'mydata' (previously assigned) in the INFILE statement to read the data.
Copied!
| 1 | DATA weight; |
| 2 | INFILE mydata; |
| 3 | INPUT idno $ week1 week16; |
| 4 | loss=week1-week16; |
12 Code Block
DATA STEP
Explanation :
Uses the fileref 'myreport' (previously assigned) in the FILE statement to conditionally write data.
Uses the fileref 'myreport' (previously assigned) in the FILE statement to conditionally write data.
Copied!
| 1 | file myreport; |
| 2 | IF loss ge 5 and loss le 9 THEN |
| 3 | put idno loss 'AWARD STATUS=3'; |
| 4 | ELSE IF loss ge 10 and loss le 14 THEN |
| 5 | put idno loss 'AWARD STATUS=2'; |
| 6 | ELSE IF loss ge 15 THEN |
| 7 | put idno loss 'AWARD STATUS=1'; |
| 8 | RUN; |
13 Code Block
%INCLUDE statement
Explanation :
Includes the content of the file referenced by 'mypgm' in the SAS job.
Includes the content of the file referenced by 'mypgm' in the SAS job.
Copied!
| 1 | %include mypgm; |
14 Code Block
FILENAME statement
Explanation :
Assigns a fileref 'mydir' to a directory or PDS (Partitioned Data Set), allowing efficient access to multiple files.
Assigns a fileref 'mydir' to a directory or PDS (Partitioned Data Set), allowing efficient access to multiple files.
Copied!
| 1 | filename mydir 'directory-or-PDS-name'; |
15 Code Block
DATA STEP
Explanation :
Reads data from the file 'qrt1.data' located in the aggregate location referenced by 'mydir'.
Reads data from the file 'qrt1.data' located in the aggregate location referenced by 'mydir'.
Copied!
| 1 | DATA weight; |
| 2 | INFILE mydir(qrt1.DATA); |
| 3 | INPUT idno $ week1 week16; |
| 4 | loss=week1-week16; |
| 5 | RUN; |
16 Code Block
DATA STEP
Explanation :
Conditionally writes data to the file 'awards' located in the aggregate location referenced by 'mydir'.
Conditionally writes data to the file 'awards' located in the aggregate location referenced by 'mydir'.
Copied!
| 1 | file mydir(awards); |
| 2 | IF loss ge 5 THEN put idno loss |
| 3 | 'AWARD STATUS=3'; |
| 4 | ELSE IF loss ge 10 |
| 5 | THEN put idno loss 'AWARD STATUS=2'; |
| 6 | ELSE IF loss ge 15 |
| 7 | THEN put idno loss 'AWARD STATUS=1'; |
| 8 | RUN; |
17 Code Block
%INCLUDE statement
Explanation :
Includes the content of the 'whole.program' file located in the aggregate location referenced by 'mydir'.
Includes the content of the 'whole.program' file located in the aggregate location referenced by 'mydir'.
Copied!
| 1 | %include mydir(whole.program); |
18 Code Block
DATA STEP
Explanation :
Reads column-wise binary data from a specified file, using INFILE options RECFM=F and LRECL=160.
Reads column-wise binary data from a specified file, using INFILE options RECFM=F and LRECL=160.
Copied!
| 1 | DATA out; |
| 2 | INFILE file-specification or path-to-file recfm=f lrecl=160; |
| 3 | INPUT var1; |
| 4 | RUN; |
19 Code Block
DATA STEP / PROC CASUTIL Data
Explanation :
This example first prepares data in a SAS WORK library, then uses PROC CASUTIL to load this data onto the CAS server. This standard method does not allow direct application of DVR optimization during loading.
This example first prepares data in a SAS WORK library, then uses PROC CASUTIL to load this data onto the CAS server. This standard method does not allow direct application of DVR optimization during loading.
Copied!
| 1 | /* Préparation des données dans WORK */ |
| 2 | DATA maTableWork; |
| 3 | INPUT PatientID $ Week1 Week8 Week16; |
| 4 | DATALINES; |
| 5 | P1 100 95 90 |
| 6 | P2 80 78 75 |
| 7 | P3 120 115 110 |
| 8 | ; |
| 9 | RUN; |
| 10 | |
| 11 | /* Méthode standard - ne permet pas l'optimisation DVR directe */ |
| 12 | PROC CASUTIL; |
| 13 | load DATA=maTableWork casout="maTableCAS"; |
| 14 | QUIT; |
20 Code Block
DATA STEP / PROC CAS Data
Explanation :
This example first creates a temporary SAS table ('somedata') with repeated values. It then uses the PROC CAS UPLOAD statement to load this table onto the CAS server, directly applying the DVR format and VARCHAR conversion options. This optimizes memory usage in a single step. A preliminary cleanup and verification step is included.
This example first creates a temporary SAS table ('somedata') with repeated values. It then uses the PROC CAS UPLOAD statement to load this table onto the CAS server, directly applying the DVR format and VARCHAR conversion options. This optimizes memory usage in a single step. A preliminary cleanup and verification step is included.
Copied!
| 1 | /* Création d'une table temporaire SAS pour l'exemple */ |
| 2 | DATA somedata; |
| 3 | LENGTH id $8 name $20 value 8; |
| 4 | DO id = 1 to 10; |
| 5 | name = cats('Item', id); |
| 6 | value = mod(id, 3) * 100; |
| 7 | OUTPUT; |
| 8 | END; |
| 9 | /* Ajout de valeurs répétées pour montrer l'efficacité de DVR */ |
| 10 | DO id = 11 to 20; |
| 11 | name = 'Repeated'; |
| 12 | value = 50; |
| 13 | OUTPUT; |
| 14 | END; |
| 15 | RUN; |
| 16 | |
| 17 | PROC CAS; |
| 18 | /* Nettoyage préalable si nécessaire */ |
| 19 | ACTION TABLE.droptable / name="somedata" caslib="casuser" quiet=true; |
| 20 | |
| 21 | /* Chargement optimisé */ |
| 22 | upload / |
| 23 | /* Récupération dynamique du chemin physique de la table SAS */ |
| 24 | path="%sysfunc(pathname(work))/somedata.sas7bdat" |
| 25 | |
| 26 | /* Configuration de la table de sortie CAS */ |
| 27 | casout={ |
| 28 | caslib="casuser" |
| 29 | name="somedata" |
| 30 | promote=true, /* Rendre la table globale */ |
| 31 | memoryformat="DVR", /* Activation de la compression DVR */ |
| 32 | replication=0 /* Ajuster la réplication selon les besoins */ |
| 33 | } |
| 34 | |
| 35 | /* Options d'importation supplémentaires */ |
| 36 | importoptions={ |
| 37 | filetype="BASESAS", |
| 38 | varcharConversion=17 /* Convertit les CHAR > 16 octets en VARCHAR */ |
| 39 | } |
| 40 | ; |
| 41 | QUIT; |
| 42 | |
| 43 | /* Vérification du format de la table en CAS (optionnel) */ |
| 44 | PROC CASUTIL; |
| 45 | list tables caslib="casuser" level="memory"; |
| 46 | 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 |
