DATA Step, Macro, Functions and more

Proc SQL - String matching in column names

Reply
Contributor
Posts: 23

Proc SQL - String matching in column names

[ Edited ]

i have a very large dataset with around 200 columns. I want to filter out columns whose names end with july, august and september using proc SQL. How do i do it?

 i tried using the string matching functionality -

proc sql;
create table v as
select * ,like '%july' , like '%august', like '%september'
from a6.smb;
quit;

However this is the wrong code. What is the correct way to do it?

is there any other way to filer out columns automatically?

Respected Advisor
Posts: 2,843

Re: Proc sql-String matching


riya275 wrote:

i have a very large dataset with around 200 columns. I want to filter out columns which end with july, august and september using proc SQL. How do i do it?


Do you mean "Columns whose name ends with july or august or september"???

 

Or do you mean columns that have values july or august or september???

 

 

 

 

 

    

--
Paige Miller
Contributor
Posts: 23

Re: Proc sql-String matching

Posted in reply to PaigeMiller

column names that end with july , aug, spet

PROC Star
Posts: 504

Re: Proc sql-String matching

[ Edited ]

In addition to @PaigeMiller answer. remember Any filtration happens in where clause and you do not have a where clause in your sql code

Contributor
Posts: 23

Re: Proc sql-String matching

but where is to filter rows and i want to filter columns

Respected Advisor
Posts: 2,843

Re: Proc sql-String matching

[ Edited ]

riya275 wrote:

but where is to filter rows and i want to filter columns


Then to filter columns, you need to obtain the column names.

 

Something like this (UNTESTED CODE)

 

proc contents data=a6.smb noprint out=_cont_;
run;

/* OBTAIN DESIRED COLUMN NAMES */
proc sql noprint;
    select name into :names separated by ',' from _cont_ where lowcase(name) ? ('july','august','september');
quit;

proc sql;
    create table v as select &names from a6.smb;
quit;

Of course, this will find any columns where july or august or september is ANYWHERE in the column name, not just at the end.

 

Having said this, I find it an extremely poor strategy to have month names in your variable names anyway, much better would be to have a column called MONTH which indicates the month of the data, rather than have the month name as part of the variable name. Further, if you use month numbers 1 to 12, you make your life even easier.

--
Paige Miller
Esteemed Advisor
Posts: 5,487

Re: Proc sql-String matching

SAS/SQL doesn't support variable lists, other than "*". You could code the list into a macro variable using dictionary tables, but the simplest way in your case is to type the variable names.

PG
Super User
Posts: 10,699

Re: Proc SQL - String matching in column names

proc transpose data=sashelp.class(obs=0) out=class;
var _all_;
run;
proc sql;
select *
 from class
  where prxmatch('/(ex|ght)$/i',strip(_name_));
quit;
Ask a Question
Discussion stats
  • 7 replies
  • 216 views
  • 2 likes
  • 5 in conversation