08-11-2014 03:32 AM
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.
08-11-2014 03:55 AM
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:
set sashelp.vcolumn (where=(libname="WORK" and memname="YOUR_DATASET")); /* Needs to be upcase */
if index(name,"_AMOUNT")>0 then output;
You also have this through SQL with the DICTONARY.TABLES and DICTIONARY.COLUMNS.
08-11-2014 06:01 AM
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.
08-11-2014 06:08 AM
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:
length lst $2000;
set vcolumns (where=(libname="SASHELP" and memname="CARS")) end=last;
if last then call symputx('LIST_OF_VARS',lst);
proc print data=sashelp.cars;