Proc SQL - String matching in column names

[riya275]

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?

[PaigeMiller]

---

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

[riya275]

column names that end with july , aug, spet

[kiranv_]

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

[riya275]

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

[PaigeMiller]

---

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

[PGStats]

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.

[Ksharp]

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;