topic Re: How to keep variables based on suffix in SAS Programming

How to keep variables based on suffix

GS2 — Wed, 06 May 2020 16:14:55 GMT

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

Re: How to keep variables based on suffix

Tom — Wed, 06 May 2020 16:16:51 GMT

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.

Re: How to keep variables based on suffix

Reeza — Wed, 06 May 2020 16:20:33 GMT

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

Re: How to keep variables based on suffix

GS2 — Wed, 06 May 2020 16:21:30 GMT

%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

Re: How to keep variables based on suffix

Reeza — Wed, 06 May 2020 16:24:15 GMT

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.

Re: How to keep variables based on suffix

Tom — Wed, 06 May 2020 16:27:35 GMT

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.

Re: How to keep variables based on suffix

GS2 — Wed, 06 May 2020 17:23:40 GMT

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.

Re: How to keep variables based on suffix

biopharma — Wed, 06 May 2020 17:33:47 GMT

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/

Re: How to keep variables based on suffix

DavePrinsloo — Wed, 06 May 2020 18:59:46 GMT

Copy the macro ut_varlist posted in:

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

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;

Re: How to keep variables based on suffix

Tom — Wed, 06 May 2020 19:01:29 GMT

@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;

Re: How to keep variables based on suffix

DavePrinsloo — Wed, 06 May 2020 19:07:55 GMT

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;

Re: How to keep variables based on suffix

GS2 — Wed, 06 May 2020 19:17:17 GMT

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,

Re: How to keep variables based on suffix

DavePrinsloo — Wed, 06 May 2020 19:28:57 GMT

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

Re: How to keep variables based on suffix

Reeza — Wed, 06 May 2020 19:40:46 GMT

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.