Using SAS Tables

Reading SAS Tables in SCL Programs

You may want to use an SCL program to manipulate column values from SAS tables. For example, you may want to do one or more of the following:

display data values in a window
use the values in arithmetic calculations
determine data values before taking certain actions.

Before a program can manipulate the data, it must read the data from the table. After column values are changed, the program can update the values of columns in the table. In addition to updating existing column values, programs also can add new rows or delete obsolete rows.

After a SAS table is open, you can access any column value for any row in the SAS table. The first step in accessing the data involves reading (or copying) a row from the SAS table to the TDV--for example, by using the FETCH function. By default, the FETCH function starts with the first row in the SAS table and reads the next row from the SAS table each time it executes.

Linking SAS Table Columns And SCL Variables

The next step in accessing the data is to link the SAS table columns in the TDV with the SCL window variables and nonwindow variables in the SDV. The function that you use depends on whether the SCL variables and SAS table columns have the same name and type. If an application has some SCL variables that match SAS table columns and others that do not, then you can use a combination of these techniques.

Matched Column and Variable Names

If columns of a SAS table and SCL variables have the same names and types, then you can use the SET routine to link all of them automatically with a single program statement. The SET routine is typically invoked immediately following the OPEN function.

Note: If you use the SET routine and then also use the PUTVARC or PUTVARN routine for an SCL variable that has a matching SAS table column, the SET routine overrides the PUTVARC or PUTVARN routine. Doing this is inefficient because duplicate actions are performed. [cautionend]

Unmatched Column and Variable Names

When the SCL variables do not have the same names or types as SAS table columns, you must use a GETVARC or GETVARN statement (for character and numeric values, respectively) for each unmatched column to link them from the TDV to the SDV. Once the columns have been manipulated, use an individual PUTVARC or PUTVARN routine to link each one from the SDV back to the TDV.

Note: The GETVARC and GETVARN functions establish only a temporary link between a SAS table column and an SCL variable. When the statement executes, the columns are linked. After the statement executes, the link is terminated. Therefore, you must use the GETVARC or GETVARN function one time for each SAS table column that you want to link. This is different from the SET routine, which establishes a permanent link between any matching SAS table and SCL variables until the open SAS table is closed. [cautionend]

Determining a Column's Position in a SAS Table

Some functions, such as GETVARC, GETVARN, PUTVARC and PUTVARN, require the position of a column in the SAS table row. Use the VARNUM function to determine the position, and then use the position repeatedly throughout your program. The following example uses the VARNUM function to determine the position of several columns. After the column positions have been determined, the program links to a labeled section called GETVALUE to determine the column values.

INIT:
   control enter;
   houses=open('sasuser.houses','u');
   if (houses=0) then _msg_=sysmsg();
   else
      do;
         vtype=varnum(houses,'style');
         vsize=varnum(houses,'sqfeet');
         vbedrms=varnum(houses,'bedrooms');
         vbathrms=varnum(houses,'baths');
         vaddress=varnum(houses,'street');
         vcost=varnum(houses,'price');
         link getvalue;
      end;
return;

MAIN:
   ...more SCL statements...
return;

TERM:
   if (houses>0) then rc=close(houses);
return;

GETVALUE:
   rc=fetch(houses);
   type=getvarc(houses,vtype);
   size=getvarn(houses,vsize);
   bedrms=getvarn(houses,vbedrms);
   bathrms=getvarn(houses,vbathrms);
   address=getvarc(houses,vaddress);
   cost=getvarn(houses,vcost);
return;

Using Table-Lookup Techniques

Table lookup, the process of looking up data in a data structure, has several useful applications for data entry applications. For example, you may want to display certain information in a window based on a value that a user has entered. If this information is stored in another SAS table, then you can use table-lookup techniques to read and display this information. In addition, you can use table lookup to perform field validation by ensuring that a value entered by a user is a value that is contained in a specified SAS table.

To validate a field value, you can use the LOCATEC, LOCATEN, or WHERE function to search a secondary SAS table for a specific character or numeric value that has been entered by a user. For example, you might want to make sure that users enter names that exist in another SAS table. You also can use these techniques to display text from a secondary SAS table, based on values that users enter in the fields. For example, when a user enters a valid name in the Employee Name field, you can look up the associated sales region and sales to date in the secondary SAS table and then display this information in the window.

Top of Page