topic Re: Proc sql-String matching in SAS Programming

Proc SQL - String matching in column names

riya275 — Fri, 05 Jan 2018 22:11:11 GMT

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?

Re: Proc sql-String matching

PaigeMiller — Fri, 05 Jan 2018 21:10:37 GMT

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

Re: Proc sql-String matching

kiranv_ — Fri, 05 Jan 2018 21:18:59 GMT

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

Re: Proc sql-String matching

PGStats — Fri, 05 Jan 2018 21:21:11 GMT

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.

Re: Proc sql-String matching

riya275 — Fri, 05 Jan 2018 21:48:40 GMT

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

Re: Proc sql-String matching

riya275 — Fri, 05 Jan 2018 21:49:54 GMT

column names that end with july , aug, spet

Re: Proc SQL - String matching in column names

Ksharp — Sat, 06 Jan 2018 10:00:34 GMT

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;

Re: Proc sql-String matching

PaigeMiller — Sat, 06 Jan 2018 15:09:27 GMT

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