HI,
Is there a way to modify the keep statement to ensure all field names containing a certain text are output? For example, I have many different balance fields in a dataset which are not part of an array and I would like to keep all fields containing the text 'balance'.
Thanks.
It's easy if the fields all start with balance, then you can use balance:
Otherwise you have to pull the variable names from the dictionary tables and create a macro variable to include in your keep statement.
proc sql noprint;
select name into :balance_list separated by " "
from sashelp.vcolumn
where upper(libname)='WORK' and upper(memname)='MY_DATA' and upper(name) like '%BALANCE%';
quit;
data want;
set have;
keep &balance_list;
run;
It's easy if the fields all start with balance, then you can use balance:
Otherwise you have to pull the variable names from the dictionary tables and create a macro variable to include in your keep statement.
proc sql noprint;
select name into :balance_list separated by " "
from sashelp.vcolumn
where upper(libname)='WORK' and upper(memname)='MY_DATA' and upper(name) like '%BALANCE%';
quit;
data want;
set have;
keep &balance_list;
run;
This works very well thanks. On a side point, why do you use the 'upper' function for the column names when the fields are in uppercase already?
They may be upper cased in your situation but they're not always upper case.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.