01-05-2018 04:04 PM - last edited on 01-05-2018 05:11 PM by Reeza
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 -
create table v as
select * ,like '%july' , like '%august', like '%september'
However this is the wrong code. What is the correct way to do it?
is there any other way to filer out columns automatically?
01-05-2018 04:10 PM
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???
01-05-2018 04:17 PM - edited 01-05-2018 04:18 PM
In addition to @PaigeMiller answer. remember Any filtration happens in where clause and you do not have a where clause in your sql code
01-06-2018 07:05 AM - edited 01-06-2018 10:09 AM
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.
01-05-2018 04:21 PM
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.
01-06-2018 05:00 AM
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;