Learning SAS? Welcome to the exclusive online community for all SAS learners.

Using column names of a dataset

Reply
New Contributor
Posts: 2

Using column names of a dataset

Hi.

I have a dataset that contains customer credit card information.The amount on various items like Airlines,transport etc. are stored in columns named Airlines_Amount,Travel_Amount etc.

What I want is to refer the column names and if they have anything like '_Amount' take their sum.

Any way to do it??I have attached a screenshot for better understanding of the problem.


Untitled.png
Super User
Super User
Posts: 7,401

Re: Using column names of a dataset

Hi,

Well, in Base SAS, not sure about what you are using, there is a library called SASHELP.  Within that are views: VTABLE - details all the tables in all the libraries, and VCOLUMN - details the columns in those tables.  So you can query these just like a normal dataset:

data want;

     set sashelp.vcolumn (where=(libname="WORK" and memname="YOUR_DATASET"));  /* Needs to be upcase */

     if index(name,"_AMOUNT")>0 then output;

run;

You also have this through SQL with the DICTONARY.TABLES and DICTIONARY.COLUMNS.

New Contributor
Posts: 2

Re: Using column names of a dataset

Hi,

Thanks a lot for the info.I have used the VCOLUMN to list out the column names I want to use.

But the problem is how do I match them with the column names in the original dataset.

I am basically trying to refer to the column names in VCOLUMN to point to the column having similar name in the original dataset.

Super User
Super User
Posts: 7,401

Re: Using column names of a dataset

Depends on what you want to do.  I would assume that you want to use columns in your program hence you are moving into macro variables/code or code generators which is a more advanced topic.  Say I want to use a list of variables in a proc print:

data _null_;

     length lst $2000;

     retain lst;

     set vcolumns (where=(libname="SASHELP" and memname="CARS")) end=last;

     lst=catx(' ',lst,name);

     if last then call symputx('LIST_OF_VARS',lst);

run;

proc print data=sashelp.cars;

     var &LIST_OF_VARS.;

run;

Ask a Question
Discussion stats
  • 3 replies
  • 314 views
  • 3 likes
  • 2 in conversation