The SAS data access functions OPEN(), FETCH(), FETCHOBS(), GETVARN(), GETVARC(), ATTRN(), ATTRNC(), VARNAME(), and CLOSE()] are powerful elements of the SAS programming language, with broad applications in both macro and DATA step contexts.
data ex1; dsid=open('sashelp.cars', 'i'); n_obs=attrn(dsid, 'nobs'); n_vars=attrn(dsid, 'nvars'); dslabel=attrc(dsid, 'label'); rc=close(dsid); run; proc print; run;
The Dataset functions are often used to get the information from SAS data sets, libraries or external files in data steps.
Open and Close Function
In SAS we use the OPEN function to access or “open” an existing data set.
OPEN(<data-set-name<, mode <, generation-number <, type>>>>)
mode specifies the type of access to the data set. There are 3 different modes by which you can open the SAS dataset.
👉I opens the data set in INPUT mode (default) and the values can be read but not modified.
👉IN opens the data set in INPUT mode. Observations are read sequentially, and you are allowed to revisit an observation.
👉IS opens the data set in INPUT mode. Observations are read sequentially, but you are not allowed to revisit an observation.
%macro ex1; %let dsid=%sysfunc(open(sashelp.class, i)); %if (&dsid=0) %then %put %sysfunc(sysmsg()); %else %put sashelp.cars data set has been opened; %mend; %ex1;
All data sets opened within a DATA step are closed automatically at the end of the DATA step. Using the CLOSE function, you can close any opened SAS data sets as soon as they are no longer needed.
The CLOSE() function closes an open dataset—this automatically happens at the end of a DATA step but is very important to remember in macro contexts.
ATTRC and ATTRN
The ATTRC and ATTRN functions are used to retrieve the value of a character and numeric attributes of a SAS data set, respectively.
ATTRN() is used to return “numeric” attributes, and ATTRC is used to return “character” attributes, but both take a data-set-id and an attribute-name as arguments.
data ex1(drop = dsid); dsid=open('sashelp.class', 'i'); nobs=attrn(dsid, 'nobs'); nvars=attrn(dsid, 'nvars'); dslabel=attrc(dsid, 'label'); run; proc print; run;
You can also use both the functions in a SAS macro as below.
%let dsid = %sysfunc(open(sashelp.class)); %put &=dsid ; %put Number of physical observations:%sysfunc(attrn(&dsid,nobs)); %put Number of variables:%sysfunc(attrn(&dsid, nvars)); %put Dataset Label: %sysfunc(attrc(&dsid,label));
The EXIST function is used to verify the existence of a SAS data library member.
The below example checks if a datasets exist or not.
%let dsname=sasuser.houses; %macro opends(name); %if %sysfunc(exist(&name)) %then %let dsid=%sysfunc(open(&name, i)); %else %put Data set &name does not exist.; %mend opends; %opends(&dsname);
The DSNAME function is used to find the data set name associated with a data set identifier.
data _null_; dsid=open('sashelp.cars', 'i'); if dsid then do; dsname=dsname(dsid); put 'The current dataset opened is : ' dsname; end; else do; error=sysmsg(); put error; end; run;OUTPUT The current dataset opened is : SASHELP.CARS.DATA
FETCH and FETCHOBS
The FETCH and FETCHOBS functions are used to read observations from a SAS data set.
sysrc contains the return code for the operation:
👉 0 successful
👉 >0 not successful
👉 <0 the operation was completed, but a warning or a note was generated. If the row is locked, it is still fetched (read in) but a sysrc of _SWNOUPD is returned.
👉 −1 the end of the table was reached
data ex2; dsid=open('sashelp.class', 'i'); fetchobs=fetchobs(dsid, 20); if (rc=-1) then _msg_='End of table has been reached.'; if (rc ne 0) then _msg_=sysmsg(); rc=close(dsid); run;
The fetch and fetchobs functions can be used in SAS macro as below.
%let dsid=%sysfunc(open(sashelp.class, i)); %let fobs=%sysfunc(fetchobs(&dsid, 3)); %let rc=%sysfunc(fetch(&dsid)); %put &fobs.;
CUROBS, NOTE, DROPNOTE, POINT, REWIND
The CUROBS, NOTE and DROPNOTE functions are used to find the current observation number, to create an observation ID (note marker) for the current observation, and to delete a note marker created by a NOTE function, respectively. The REWIND function will move the data set pointer to the beginning of a SAS data set.
The variable functions are used to find the properties of a character variable or numeric variable, respectively. For example, The VARTYPE function can be used to find the type of a variable (numeric or character type).
VARNUM returns the number of a variable’s position in a SAS data set, or 0 if the variable is not in the SAS data set. This is the same variable number that is next to the variable in the output from PROC CONTENTS.
If we know the number of a variable’s position in the data set, the VARNAME function can be used to find the corresponding variable name.
The VARTYPE function can be used to find the type of variable (numeric or character type).
The VARFMT, VARINFMT, and VARLABEL functions are used to find the format, informat and label assigned to a data set variable.
You can find the length of a variable using the VARLEN function.
data vars; length name $ 8 type $ 1 format informat $ 10 label $ 40; drop dsid i num rc; dsid=open("sashelp.cars", "i"); num=attrn(dsid, "nvars"); do i=1 to num; name=varname(dsid, i); type=vartype(dsid, i); format=varfmt(dsid, i); informat=varinfmt(dsid, i); label=varlabel(dsid, i); length=varlen(dsid, i); position=varnum(dsid, name); output; end; rc=close(dsid); run; proc print;
GETVARC and GETVARN
The GETVARC and GETVARN functions are used to get the values of character and numeric variables.
data ex4; dsid=open('sashelp.class', 'i'); position=varnum(dsid, 'age'); fetchobs=fetchobs(dsid, 2); getvarn=getvarn(dsid, position); user=getvarc(dsid, varnum(dsid, 'name')); rc=close(dsid); run;
EXTERNAL FILE FUNCTIONS
Many external file functions are available now. These include functions for directories and for files. The names of functions for directories begin with a D, like in DCLOSE and DNUM. The names of functions for files begin with an F, like in FGET and FEXIST. Many data set functions, like EXIST, NOTE, POINT, REWIND and so on, have the corresponding external file functions.
The FOPEN and FCLOSE functions are used for external files and the DOPEN and DCLOSE functions are used for directories. The arguments of both FOPEN and DOPEN use the file reference assigned to a file or a directory.
FOPEN (fileref <,open-mode <,record-length <,record-format>>>)
Open modes of external files
|A||With the APPEND mode, new records can be written after the current end of the file.|
|I||This is the default mode. It opens the file as read-only.|
|O||OUTPUT mode defaults to the OPEN mode specified in the operating environment option in the FILENAME statement or function. If no operating environment option is specified, it allows writing new records at the beginning of the file.|
|S||Sequential input mode is used for pipes and other sequential devices such as hardware ports.|
|U||With the UPDATE mode file can be open for reading and writing. The file is created if it does not exist.|
Use the OUTPUT mode with care. Opening an existing file for output overwrites the current contents of the file without warning.
The FILENAME function can be used to assign or de assign a file reference for an external file, directory, or output device. The FILEREF function is used to verify if a file reference has been assigned. A file can also be opened by directory ID and member name using the MOPEN function.
Note: If there is an invalid argument, FOPEN function returns 0. Invalid arguments do not produce a message in the SAS log and do not set the _ERROR_ automatic variable. You can find the text of the corresponding error message from the SYSMSG function.
The FEXIST and FILEEXIST functions are used to verify the existence of an external file. FEXIT uses an associated file reference FILEEXIST uses the physical name of the file.
We can use the FDELETE function to delete an external file, and the FRLEN function to find the size of the last record read.
USING LIBRARY AND CATALOG FUNCTIONS
The CEXIST, LIBNAME and LIBREF functions can be used in data step to verify the existence of a SAS catalog or SAS catalog entry, to assign or deassign a library reference for a SAS data library, and to verify that a library reference has been assigned, respectively. The following is an example of using these functions.
The LIBREF function returns 0 if the libref has been assigned, or returns a nonzero value if the libref has not been assigned.
%if (%sysfunc(libref(sashelp))) %then %put %sysfunc(sysmsg());