Reduce hard coding by using the VCOLUMN table in SASHELP

Biguidance (Instructor)

(OP)

31 Jan 12 06:10

Reduce hard coding by using the VCOLUMN table in SASHELP to get a list of your variables into a macro variable array.

Very often it is useful to have a list of all the variables you have in a certain table. You don't always want to type them out yourself (hard coding) but instead want an automatically generated list based on what is present in the table.

Think about a regression. You can either type all regressor variables yourself or you load a list into a macro variable and then use that macro variable in the regression equation.

Here is an example of how to make this list by DATA step (though PROC SQL is often easier and preferred over the DATA STEP when it comes to loading values into macro variables).

CODE

DATA;

          SET SASHELP.VCOLUMN

(WHERE=(LIBNAME = "libraryoftable" AND MEMNAME = "tablename"));

          CALL SYMPUT(COMPRESS('allvariables'||_N_),name);

          CALL SYMPUT('nbr_of_variables'),_N_);

RUN;

SASHELP.VCOLUMN is a metadata table. It contains data about data. In this case it contains the column-names for every table you have. In order to get the variables you want you will need to select the correct table and this is done by putting a restriction on both the library and table name (often just table name is enough but if you are in a habit of reusing the same table names over different libraries, you are best to select on both). In the example this is done with the WHERE clause. The MEMNAME (member name) is a variable in the VCOLUMN metadata table which contains all the table names.

Here we upload the contends of the name variable to macro variables by using CALL SYMPUT. A combination of _N_ and a chosen macrovariable name (here 'allvariables') will make the datastep construct our macro variable array.

We also keep the number of variables in a macro variable called 'nbr_of_variables' so we can know at what number the array ends.

In order to check whether your macro variables are created correctly you can use %PUT. This will make SAS put the contents of the variables in the log.

CODE

%PUT &allvariables1 &allvariables2 &allvariables3;

CODE

%PUT &nbr_of_variables;

Calling the first variable of our array is done by resolving &allvariables1. Calling the last one however is a bit more tricky since it requires us to know how many of these macro variables were actually created. Luckily we kept that information in the macro variable &nbr_of_variables.

In order to get the last macro variable of our array we simply combine the two macro names as so (in this article we do not explain how macro variables resolve, you can find more on this in our library or by following the MACRO courses).

CODE

%PUT &&allvariables&nbr_of_variables;

Now you are ready to resolve these macro variables anywhere by building a simple macro loop;

CODE

%MACRO printmacroarray;

          %DO i = 1%TO &nbr_of_variables;

                   %PUT &&allvariables&i;

          %END;

%MEND printmacroarray;

%printmacroarray;

BIGuidance
www.biguidance.com

Come Join Us!

Posting Guidelines

Reduce hard coding by using the VCOLUMN table in SASHELP