SAS Data Access Functions

For the past few years, I’ve been learning SAS and have found that most of the functions I use are available in the SAS Language Reference. To help you get started with some of these functions, I’ve put together a quick guide to help you learn how to use these functions.

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;

Dataset Functions

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

We use the OPEN function in SAS 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 three different modes by which you can open the SAS dataset.

I open 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 Functions

The ATTRC and ATTRN functions 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));

Exist Function

The EXIST function is used to verify the existence of a SAS data library member.

The below example checks if 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);

Dsname Function

The DSNAME function finds 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.

Syntax:

sysrc=FETCHOBS(table-id,row-number<,options> );

The value of sysrc contains the return code for the operation. Below are 4 return codes based on whether the operation was successful.

• 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 also 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.

Variable Functions

The variable functions are used to find the properties of a character variable or numeric variable. For example, The VARTYPE function can be used to find the type of a variable (numeric or character type).

Varnum,vartype,varfmt,varlabel,varlen,varnum

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 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 find the variable type (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 Functions

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 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.

Fopen,fclose,dopem,dclose

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.

Syntax

FOPEN (fileref <,open-mode <,record-length <,record-format>>>)

Open modes of external files

Use the OUTPUT mode with care. Opening an existing file for output overwrites the file’s current contents 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.

Read – Retrieve file size or last modified date of an external file

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 the data step to verify the existence of a SAS catalog or SAS catalog entry, to assign or reassign 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 a nonzero value if the libref has not been assigned.

%if (%sysfunc(libref(sashelp))) %then
  %put %sysfunc(sysmsg());