How to keep variables based on suffix

[GS2]

Using SAS 9.4

I have 84 variables in a data set and half of them are labeled "_co". I want to keep only the variables with "_co". I have seen a lot of code for how to drop based on suffix but I have not seen any accepted answers on how to keep. The other half of the variables do not have a suffix or prefix that would make it easy to drop based on that. Any help or guidance would be appreciated. Thank you

[Tom]

Just use the code you found and change the keyword DROP to the keyword KEEP.

Try it. If it doesn't work then post an actual example and either show the error or explain how it is not doing what you want.

[GS2]

%let suffix=_co;

proc sql noprint;
select name into :vars separated by ' '
from dictionary.columns
where upcase(libname)=upcase('work')
and upcase(memname)=upcase('have')
and upcase(substr(name,length(name)-(length("&suffix")-1),length("&suffix")))=upcase("&suffix");
quit;

%put &vars;

data cases;
set work.have;
if length("&vars") ne 0 then do;
drop &vars;
end;
run;

This is the code I ran

[Reeza]

DROP/KEEP statements are not conditional so that IF/THEN does nothing.

Change DROP to KEEP and it will keep the variables. The SQL just creates the list of variables to keep that you can slot in to the data step.

Open the sashelp.vcolumn table to see what's in it and how it's being used.

[Tom]

So do you want to find the variables to KEEP or those to DROP?

You posted code that is using the list of variable in a DROP statement, not a KEEP statement.

[GS2]

I want to keep the "_co". I want to split the data in 2, so I dropped the "_co" in 1 but want to KEEP in a new data set. 

[Tom]

---

@GS2 wrote:

I want to keep the "_co". I want to split the data in 2, so I dropped the "_co" in 1 but want to KEEP in a new data set. 

---

So take the macro variable with the list of variable names and use it in two places.  You can do the split in one step if you want by using dataset options on the output datsets.

data cases_with(keep=&vars) case_without(drop=&vars);
  set work.have;
run;

[Reeza]

Can you backwards in your step and change your naming convention so that you have prefix rather than a suffix? It seems like that change could save you a ton of time downstream.

[Reeza]

Here is a reference that illustrates how to refer to variables and datasets in a short cut list:
https://blogs.sas.com/content/iml/2018/05/29/6-easy-ways-to-specify-a-list-of-variables-in-sas.html

Can you use one of the other shortcut methods to list out the variables you want to keep/drop?

---

@GS2 wrote:

Using SAS 9.4

I have 84 variables in a data set and half of them are labeled "_co". I want to keep only the variables with "_co". I have seen a lot of code for how to drop based on suffix but I have not seen any accepted answers on how to keep. The other half of the variables do not have a suffix or prefix that would make it easy to drop based on that. Any help or guidance would be appreciated. Thank you

---

[biopharma]

You may want to look at Rick Wilkin's example of how he has used SAS Jedi's macros to keep variables with a certain suffix. Looks like what you want.  https://blogs.sas.com/content/iml/2019/10/28/regular-expressions-variable-names-sas.html

You will need to download the macros from Jedi's post and they may be useful later for a lot of other tasks.

https://blogs.sas.com/content/sastraining/2017/08/29/sas-variable-lists-by-pattern/

[DavePrinsloo]

Copy the macro ut_varlist posted in:

https://communities.sas.com/t5/SAS-Programming/Macro-function-to-return-a-list-of-variables-in-a-tab...

to your macro library.  Then you can use it to build macro variable lists embedded in code, for example:

Data want:
set have(keep=%ut_varlist(table=have,contain=_co, contain_pos=end));
run;

OR

proc sql noprint;
create table want as
select %ut_varlist(table=have,contain=_co, contain_pos=end), newdlm=%str(,))
from have;
quit;

[DavePrinsloo]

Further example:

%let co_vars=%ut_varlist(table=have, contain=_co, contain_pos=end);
%let other_vars=%ut_varlsit(table=have, exclude=&co_vars.);

data want_co (keep=&co_vars.)
        want_others(keep=&other_vars.);
set have;
run;

[GS2]

I ran the second part of the OR option and got the following error

37 proc sql noprint;
38 create table controls as
39 select %ut_varlist(table=bds.knee_scope,contain=_co, contain_pos=end), newdlm=%str(,))
-
22
200
WARNING: Apparent invocation of macro UT_VARLIST not resolved.
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string,
a numeric constant, a datetime constant, a missing value, (, *, +, -, ALL, BTRIM,
CALCULATED, CASE, DISTINCT, EXISTS, INPUT, NOT, PUT, SUBSTRING, TRANSLATE, UNIQUE,

[DavePrinsloo · Accepted solution]

YOu need to copy the macro attached to the link https://communities.sas.com/t5/SAS-Programming/Macro-function-to-return-a-list-of-variables-in-a-tab...
and submit it first

The best would be to save ut_varlist.sas to a path referenced in the sasutos macro definitions, or to %include it