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?
@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???
column names that end with july , aug, spet
In addition to @PaigeMiller answer. remember Any filtration happens in where clause and you do not have a where clause in your sql code
but where is to filter rows and i want to filter columns
@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.
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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.