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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.