×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Reduce hard coding by using the VCOLUMN table in SASHELP

Reduce hard coding by using the VCOLUMN table in SASHELP

Reduce hard coding by using the VCOLUMN table in SASHELP

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close