HomeAdvanced SAS Programming

SAS Data Access Functions8 min read

Like Tweet Pin it Share Share Email

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;

SAS Data Access Functions

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

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;

SAS Data Access Functions

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

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);

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=FETCHOBS(table-id,row-number<,options> );

 

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.

Variable Functions

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,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 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;

SAS Data Access Functions

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;

SAS Data Access Functions 1

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.

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

AWith the APPEND mode, new records can be written after the current end of the file.
IThis is the default mode. It opens the file as read-only.
OOUTPUT 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.
SSequential input mode is used for pipes and other sequential devices such as hardware ports.
UWith 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.

NoteIf 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 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());

 

Comments (0)

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.