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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.