I want to write a macro, by using which some columns, if the column name contains a certain string, will then be deleted/dropped.
For example, one of the dataset will be like:
LIB.Original_1
ID vector_A vector_B factor_C
1 a b c
2 a2 b2 c2
after macro, it should be something like:
LIB.Page_1
ID factor_C
1 c
2 c2
The macro would be something like:
%macro dropCol(_name=);
....
proc sql;
select name into :droplist separated by ' '
from dictionary.columns
where libname = 'LIB' and memname = 'Original_'&_name
and name like '%vector%';
quit;
data LIB.Page_&_name;
set LIB.Original_&_name;
drop &droplist;
run;
...
%mend dropCol;
%dropCol(_name=1);
%dropCol(_name=2);
%dropCol(_name=3);
%dropCol(_name=4);
However it's not working at the moment. The error reported currently comes from '&droplist'.
It'd be great if someone can fix the codes above or provide other ways of doing the task.
The Error is:
41 proc sql; select name into :droplist separated by '' from dictionary.columns where libname = 'input' and memname =
41 ! "final_&_name" and name like '%Est_%'; quit; data work.temp_&_name; set input.final_&_name; drop &droplist; run;
_
22
2 The SAS System 08:47 Thursday, February 9, 2017
ERROR 22-322: Syntax error, expecting one of the following: a name, ;, _ALL_, _CHARACTER_, _CHAR_, _NUMERIC_.
NOTE: Line generated by the invoked macro "REMOVEE".
41 proc sql; select name into :droplist separated by '' from dictionary.columns where libname = 'input' and memname =
41 ! "final_&_name" and name like '%Est_%'; quit; data work.temp_&_name; set input.final_&_name; drop &droplist; run;
_
200
ERROR 200-322: The symbol is not recognized and will be ignored.
MPRINT(REMOVEE): set input.final_2016;
WARNING: Apparent symbolic reference DROPLIST not resolved.
MPRINT(REMOVEE): drop &droplist;
MPRINT(REMOVEE): run;
WARNING: The variable droplist in the DROP, KEEP, or RENAME list has never been referenced.
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.TEMP_2016 may be incomplete. When this step was stopped there were 0 observations and 42 variables.
WARNING: Data set WORK.TEMP_2016 was not replaced because this step was stopped.
Since your Proc sql generates error you really should post the log with the error. Use OPTIONS MPRINT to show more details in the log.
Try
proc sql; select name into :droplist separated by ' ' from dictionary.columns where libname = 'LIB' and memname = "Original_&_name" /*<= the ' before name was proximate cause of error*/ /* place inside double quotes " " to resolve the macro varialbe*/ and name like '%vector%'; quit;
Data want;
set have(drop=vector:);
run;quit;
Hey it works for columns names like: vector_4, vectorETC.
Would you kindly provide a solution for names like ETC_vector_ETC, ETC_vector?
Thanks.
If the string is a common prefix, approach this like a drop statement, but just use a colon after the prefix
data table2;
set table;
drop column_prefix: ;
run;
You should make sure to set DROPLIST empty before the query in case the SELECT statement does not find ANY variables.
The DROP statement does not like it when no variables are listed. But the DROP= dataset option doesn't mind.
Note that the LIBNAME and MEMNAME variable in the dictionary tables are always in uppercase. So you will never find any records where
libname = 'input' and memname = "final_&_name"
Plus I doubt that you have any member names with an & in their names. That would be very hard to create and difficult to reference.
Also note that the NAME field will store the names in mixed case. So a name like Vector1 or VECTOR2 will never match:
name like '%vector%'
Try something like this instead:
proc sql noprint;
%let droplist=;
select name
into :droplist separated by ' '
from dictionary.columns
where libname = 'LIB'
and memname = "ORIGINAL_%upcase(&_name)"
and lowcase(name) like '%vector%'
;
quit;
data LIB.Page_&_name;
set LIB.Original_&_name(drop=&droplist);
run;
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!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.