Subset data by column, using wildcards?

tennytivvytutu — Fri, 08 Jul 2022 18:18:33 GMT

Hi there,

I know that we can use the LIKE operator to filter rows of data; below are my notes on this:

LIKE operator for pattern matching

WHERE col-name LIKE "value";

% is a wild card for any number of characters

_ is a wild card for a single character

*A wild card is a character that can be substituted for zero or more characters in a string

WHERE City LIKE "New%"; will return values like New York, Newport, New
WHERE City LIKE "Sant_ %"; will return values like Santa Clara, Santo Domingo

My question is, is it possible to apply this same logic to subset data by column names?

More specifically, I have a table with over 100 columns; I want to create a new table that includes only the columns related to Math. Instead of copy/paste or listing all the columns with the word "Math" in the name, can I apply the LIKE operator using the wildcards to create a table including all columns whose name contains the word "Math"?

Moreover, would this subsetting need to happen in a DATA step or PROC SQL?

DATA want; SET have; WHERE *column name; LIKE "%math%"; RUN;

PROC SQL; CREATE TABLE want AS

SELECT *column name LIKE '%math'; FROM have;

QUIT;

Thanks in advance!

Re: Subset data by column, using wildcards?

sbxkoenk — Fri, 08 Jul 2022 19:39:54 GMT

Use the dictionary tables, like here :

%LET olnames=;
PROC SQL noprint;
 select name into :olnames separated by ' '
 from dictionary.columns
 where libname='SASHELP' and memname='HEART' and name LIKE "%ol%";
QUIT;
%PUT &=olnames;

data work.have; set sashelp.heart(keep=&olnames.); run;
/* end of program */

Koen

topic Subset data by column, using wildcards? in SAS Programming

Subset data by column, using wildcards?

Re: Subset data by column, using wildcards?